How to add date column to the table

Question

I need to add date field column to the current table.

I need to add Jan1 to Dec31 of 2018 to the column named date.

I tried with following query,

 Insert into [dbo].[Calendar]  ([Date])
 Values
 Getdate()

It throws syntax error near getdate().

Please help me with the query.

Possible duplicate of: [How to generate a range of dates in SQL Server](https://stackoverflow.com/questions/11141507/how-to-generate-a-range-of-dates-in-sql-server). — Tim Biegeleisen, Oct 29 '18 at 06:22
That link is explaining about particular dates, how to simple add the dates without using variables? — krishna31, Oct 29 '18 at 06:38

score 2 · Answer 1 · edited Oct 29 '18 at 10:51

2

You can try below using recursive cte

DECLARE @startDt date='2018-01-01'
DECLARE @endtDt date='2018-12-31'    

;WITH GetDates As  
(  
SELECT 1 AS [COUNTER], @startDt as [Date]
UNION ALL  
SELECT [COUNTER] + 1, DATEADD(DAY,1,[Date])  
FROM GetDates  
WHERE [Date] < @endtDt   
)  
SELECT [DATE] FROM GetDates 
OPTION (MAXRECURSION 0)

edited Oct 29 '18 at 10:51

Sreenu131

2,476
1
7
18

answered Oct 29 '18 at 06:41

Fahmi

37,315
5
22
31

1

your script has thrown conversion issues – Sreenu131 Oct 29 '18 at 10:46

Sushil Sharma · Answer 2 · 2018-10-29T06:40:20.553

0

wrap getdate() inside braces.

Insert into [dbo].[Calendar]  ([Date])
 Values
 (Getdate())

And for adding Jan1 to Dec31 of 2018 use below code

declare @startDt date='2018-01-01'
declare @endtDt date='2018-12-31'
while (@startDt<=@endtDt)
begin
    Insert into [dbo].[Calendar]  ([Date]) Values (@startDt)
    set @startDt=dateadd(dd,1,@startDt)
end

edited Oct 29 '18 at 06:40

answered Oct 29 '18 at 06:35

Sushil Sharma

81
9

Thanks, but this command adds only to 1 row, but how to add all 365 days? – krishna31 Oct 29 '18 at 06:36
Although this is correct, doing a loop isn't the best way, performance-wise. – Radu Gheorghiu Oct 29 '18 at 06:41

score 0 · Answer 3 · answered Oct 29 '18 at 10:32

Try this without using Recursive Cte

DECLARE @startDt date='2018-01-01'
DECLARE @endtDt date='2018-12-31'

SELECT DATEADD(DAY,Number,@startDt) AS RecurringDates
FROM Master.dbo.spt_values
WHERE [type] ='P'
AND DATEADD(DAY,Number,@startDt) BETWEEN @startDt AND @endtDt
ORDER BY 1

How to add date column to the table

3 Answers3