25

What is the easiest way to populate a temp table with dates including and between 2 date parameters. I only need the 1st day of the month dates.

So for example if @StartDate = '2011-01-01' and @EndDate = '2011-08-01'

Then I want this returned in the table

2011-01-01
2011-02-01
2011-03-01
2011-04-01
2011-05-01
2011-06-01
2011-07-01
2011-08-01
mameesh
  • 3,651
  • 9
  • 37
  • 47

7 Answers7

24

This works even if the @StartDate is not the first of the month. I'm assuming that if it's not the start of the month, you want to begin with the first of the next month. Otherwise remove the +1.:

;WITH cte AS (
SELECT CASE WHEN DATEPART(Day,@StartDate) = 1 THEN @StartDate 
            ELSE DATEADD(Month,DATEDIFF(Month,0,@StartDate)+1,0) END AS myDate
UNION ALL
SELECT DATEADD(Month,1,myDate)
FROM cte
WHERE DATEADD(Month,1,myDate) <=  @EndDate
)
SELECT myDate
FROM cte
OPTION (MAXRECURSION 0)
GilM
  • 3,711
  • 17
  • 18
  • 1
    Running example as-is results in a single record. What am I missing? – Elan Hasson Jul 11 '14 at 16:49
  • @ElanHasson, Did you set StartDate and Endate in different months (if StartDate isn't the first of the month, this uses the first of the next month). – GilM Jul 22 '14 at 18:51
  • Thats what I was missing. I used Nitin's solution. – Elan Hasson Jul 23 '14 at 21:03
  • Just came back this this because it was upvoted recently. For SQL 2012 or later, the ELSE expression should be replaced with: ELSE DATEADD(Day,1,EOMONTH(@StartDate)) END AS myDate – GilM May 23 '16 at 19:21
18
declare @StartDate date = '2014-01-01';
declare @EndDate date = '2014-05-05';

;WITH cte AS (
    SELECT @StartDate AS myDate
    UNION ALL
    SELECT DATEADD(day,1,myDate) as myDate
    FROM cte
    WHERE DATEADD(day,1,myDate) <=  @EndDate
)
SELECT myDate
FROM cte
OPTION (MAXRECURSION 0)
manlio
  • 18,345
  • 14
  • 76
  • 126
Nitin Vartak
  • 181
  • 1
  • 2
9
declare @StartDate datetime
declare @EndDate datetime
select @StartDate = '2011-01-01' ,  @EndDate = '2011-08-01'

select @StartDate= @StartDate-(DATEPART(DD,@StartDate)-1)

declare @temp  table
(
TheDate datetime
)
while (@StartDate<=@EndDate)
begin
insert into @temp
values (@StartDate )
select @StartDate=DATEADD(MM,1,@StartDate)
end
select * from @temp

Works even if the @StartDate is not the first day of the month by going back to the initial day of the month of StartDate

Icarus
  • 63,293
  • 14
  • 100
  • 115
7

this is tested in SQL 2008 R2

Declare @StartDate datetime = '2015-03-01'
Declare @EndDate datetime = '2015-03-31'
declare @temp Table
(
DayDate datetime
);

WHILE @StartDate <= @EndDate
begin
 INSERT INTO @temp (DayDate) VALUES (@StartDate);
 SET @StartDate = Dateadd(Day,1, @StartDate);
end ;

select * from @temp

Result:

DayDate
-----------------------
2015-03-01 00:00:00.000
2015-03-02 00:00:00.000
2015-03-03 00:00:00.000
2015-03-04 00:00:00.000
...
mhd ghaith Maarawi
  • 71
  • 1
  • 3
2

Interestingly, it is faster to create from enumerated data as per this article.

DECLARE @StartDate DATE = '10001201';
DECLARE @EndDate DATE   = '20000101';

DECLARE @dim TABLE ([date] DATE)

INSERT @dim([date])
SELECT d
FROM
(
  SELECT
      d = DATEADD(DAY, rn - 1, @StartDate)
  FROM 
  (
      SELECT TOP (DATEDIFF(DAY, @StartDate, @EndDate)) 
          rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
      FROM
          sys.all_objects AS s1
      CROSS JOIN
          sys.all_objects AS s2
      ORDER BY
          s1.[object_id]
  ) AS x
) AS y;

On my machine, it's around 60% faster with large date ranges. The recursion method can populate 2000 years worth of data in around 3 seconds though, and looks a lot nicer, so I don't really recommend this method just for incrementing days.

Vojtěch Dohnal
  • 7,867
  • 3
  • 43
  • 105
Richard
  • 389
  • 1
  • 13
1

Correction for null dates:

IF OBJECT_ID('tempdb..#dim') IS NOT NULL 

    DROP TABLE #dim

CREATE  TABLE #dim ([date] DATE)

if not @Begin_Date is null and not @End_Date is null

begin
    INSERT #dim([date])
    SELECT d
    FROM(

      SELECT
          d = DATEADD(DAY, rn - 1, @Begin_Date)
      FROM 
      (
          SELECT TOP (DATEDIFF(DAY, @Begin_Date, @End_Date)) 
              rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
          FROM
              sys.all_objects AS s1
          CROSS JOIN
              sys.all_objects AS s2
          ORDER BY
              s1.[object_id]
      ) AS x
    ) AS y;
end
Cœur
  • 37,241
  • 25
  • 195
  • 267
Damien Astolfi
  • 11
  • 1
0
CREATE TABLE #t (d DATE)

INSERT INTO #t SELECT GETDATE()

GO

INSERT #t SELECT DATEADD(DAY, -1, MIN(d)) FROM #t

GO 10
Michal
  • 2,078
  • 23
  • 36
Jiří Sedlák
  • 11
  • 1