Select all dates between first day of month and current date

Question

Just want to select all dates between cureent date and first day of month . I want to use fill all dates in a temp table

declare @temp table 
 (
   ddate datetime 
  )

I have tried with with cte and i don't want to use while loop becuase i want to avoid while in stored procedure .

For example as today is 11-oct-2012

so temp table should have 11 rows starting from 1-oct-2012 to 11-oct-2012

Why don't you want to use a while loop? – tobias86 Oct 11 '12 at 07:09 — tobias86, Oct 11 '12 at 07:09

Niladri Biswas · Accepted Answer · 2012-10-11T07:20:09.770

Try this

DECLARE @startDate DATE=CAST(MONTH(GETDATE()) AS VARCHAR) + '/' + '01/' +  + CAST(YEAR(GETDATE()) AS VARCHAR)  -- mm/dd/yyyy
DECLARE @endDate DATE=GETDATE() -- mm/dd/yyyy

SELECT [Date] = DATEADD(Day,Number,@startDate) 
FROM  master..spt_values 
WHERE Type='P'
AND DATEADD(day,Number,@startDate) <= @endDate

OR

DECLARE @startDate DATETIME=CAST(MONTH(GETDATE()) AS VARCHAR) + '/' + '01/' +  + CAST(YEAR(GETDATE()) AS VARCHAR) -- mm/dd/yyyy
DECLARE @endDate DATETIME= GETDATE() -- mm/dd/yyyy

;WITH Calender AS 
(
    SELECT @startDate AS CalanderDate
    UNION ALL
    SELECT CalanderDate + 1 FROM Calender
    WHERE CalanderDate + 1 <= @endDate
)
SELECT [Date] = CONVERT(VARCHAR(10),CalanderDate,25) 
FROM Calender
OPTION (MAXRECURSION 0)

enter image description here

score 3 · Answer 2 · answered Oct 11 '12 at 07:56

3

declare @temp table (ddate datetime);

insert @temp
select DATEDIFF(d,0,GetDate()-Number)
from master..spt_values
where type='p' and number < DatePart(d,Getdate())
order by 1;

answered Oct 11 '12 at 07:56

RichardTheKiwi

105,798
26
196
262

your answer is very good , short and i use this but @Biswas answer is before so i accepted that one as answer because that also give me correct record. – rahularyansharma Oct 11 '12 at 09:17
one more thing you have calculated days from 1900 ? am i rt ? so its possible that days count overflow and its not give us correct answer ? i have just doubt .. – rahularyansharma Oct 11 '12 at 09:47
No I have not calculated from 1900. `Datediff(d,0` is only used to strip the time from `GetDate()-Number` which only produces dates in the current month. – RichardTheKiwi Oct 11 '12 at 17:35
Hey @RichardTheKiwi http://stackoverflow.com/questions/12853319/combine-today-and-running-total-results-in-one-sql-query for you and i am sure you can solve this with in seconds..... – rahularyansharma Oct 12 '12 at 06:37

score 0 · Answer 3 · answered Jun 02 '18 at 03:15

0

use current_date add days of every the generated series to the days, the series could from the first day of the month subtract current_day to 0, such today 02 Jun, the series could be (-1, 0).

answered Jun 02 '18 at 03:15

LiangXunfly

3
2

score 0 · Answer 4 · edited Oct 01 '19 at 15:45

Try the following code:

DECLARE @startDate DATETIME=CAST(MONTH(GETDATE()) AS VARCHAR) + '/' + '01/' +  + CAST(YEAR(GETDATE()) AS VARCHAR) -- mm/dd/yyyy
DECLARE @endDate DATETIME= CAST(MONTH(GETDATE()) AS VARCHAR) + '/' + '31/' +  + CAST(YEAR(GETDATE()) AS VARCHAR) -- mm/dd/yyyy

;WITH Calender AS 
(
    SELECT @startDate AS CalanderDate
    UNION ALL
    SELECT CalanderDate + 1 FROM Calender
    WHERE CalanderDate + 1 <= @endDate
)
SELECT [Date] = CONVERT(VARCHAR(10),CalanderDate,25) 
FROM Calender
OPTION (MAXRECURSION 0)

Select all dates between first day of month and current date

4 Answers4