0

I am using SQL Server and have a table (Table_Date) with a 'date' field. I want to insert all 2015 dates in this field.

It should have 365 distinct rows, 1 row for each day of 2015.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sam
  • 103
  • 7
  • possible duplicate of [How i can Insert Dates in the table from given date Ranges](http://stackoverflow.com/questions/21299773/how-i-can-insert-dates-in-the-table-from-given-date-ranges) – abl Jan 26 '15 at 19:45

3 Answers3

2

One method is with a recursive CTE:

with dates as (
      select cast('2015-01-01' as date) as thedate
      union all
      select dateadd(day, 1, thedate)
      from dates
      where thedate < '2015-12-31'
     )
select *
from dates
option (maxrecursion 0);

An alternative is to use a table that has at least 365 rows. master..spt_values is often used for this purpose:

select dateadd(day, seqnum - 1, '2015-01-01')
from (select row_number() over (order by ()) as seqnum
      from master..spt_values
     ) t
where seqnum <= 365;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks Gordon for suggesting two solutions for my problem. I tried to run the recursive CTE but it gave the following error. Msg 1035, Level 15, State 10, Line 2 Incorrect syntax near 'cast', expected 'AS'. Msg 102, Level 15, State 1, Line 6 Incorrect syntax near ')'. – Sam Jan 26 '15 at 19:51
  • @Sam . . . I left the type out of the cast. – Gordon Linoff Jan 27 '15 at 22:19
1

Here's one way:

CREATE TABLE #nums(num INT);
INSERT INTO #nums VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

WITH cteDays AS
(
    SELECT 100*d100.num + 10*d10.num + d1.num AS YearDay
    FROM       #nums AS d1
    CROSS JOIN #nums AS d10
    CROSS JOIN #nums AS d100
    WHERE d100.num <=3
)
SELECT CAST('2015-01-01' AS DATETIME) + YearDay AS YearDate
FROM    cteDays
WHERE   YEAR(CAST( CAST('2015-01-01' AS DATETIME) + YearDay AS DATETIME)) = 2015
RBarryYoung
  • 55,398
  • 14
  • 96
  • 137
0

Something like this could work as well:

declare @count int = 0

while (@count < 365)
begin
    --make this the insert
    select DATEADD(DD, @count, getdate())
    set @count = @count + 1
end

Not sure what context this will applied to though... This is very basic, but if this is a one-time event it won't matter.

macoms01
  • 1,110
  • 13
  • 22