Insert all dates in a time period in a table

Question

I have a table having two columns (date_ID, entry_Date). I want to insert all the dates within a specific time period into the table (say dates all the dates between 2002-2030). Is there any way to do that using loops in SQL-Server?

Answer 1

Try this

DECLARE @d date='20020101' 
WHILE @d<'20300101'
    BEGIN
        INSERT INTO dbo.Dates (entry_Date)
        VALUES (@d)
        SET @d=DATEADD(DAY,1,@d)
    END
GO

Answer 2

This should do it:

WITH TestItOut AS
(
    SELECT CAST('2002-01-01' as datetime) DateColumn
    UNION ALL
    SELECT DateColumn + 1
    FROM TestItOut
    WHERE DateColumn + 1 <= '2030-12-31'
)

INSERT INTO YourTable (ColumnName)
SELECT DateColumn
FROM TestItOut
OPTION (MAXRECURSION 0)

Answer 3

in oracle i would do

insert into sometable 
select to_date('01/01/2013','dd/mm/yyyy') + level 
from dual 
connect by level < 10001

this will generate 10000 dates from 1/1/13 with a daily interval. if you want hourly interval for example you can just change + level to + level/24.

this is basic ANSI sql hierarchical query - it should work in SQL server as well.