CODEWITHSUNDEEP
sqlsql-server
AfterGlow
AfterGlow

Reputation: 227

Insert all dates in a time period in a table

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?

Upvotes: 7

Views: 7806

Answers (4)

haki
haki

Reputation: 9779

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.

Upvotes: 2

mccee
mccee

Reputation: 1667

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)

Upvotes: 5

Igor Borisenko
Igor Borisenko

Reputation: 3866

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

Upvotes: 7

Freelancer
Freelancer

Reputation: 9074

insert into table values(date_ID,(select entry_Date from table where entry_Date between 01/01/2002 and 01/01/2030))

Try this kind of query.

In place of date_ID put your appropriate value.

Upvotes: 0

Related Questions