insert list of dates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

insert list of dates

Author

Topic

nice123ej
Starting Member

48 Posts

Posted - 2008-10-17 : 09:46:35

hi
i have a table that will take list of dates
i.e. the table will have records for each day in a period
example : this table will take 5 records for period between 24 Jun 2008 and 28 Jun 2008

XTable
xid xdate
-------------------
1 24 Jun 2008
2 25 Jun 2008
3 26 Jun 2008
4 27 Jun 2008
5 28 Jun 2008

this table will be built based on start date (24 Jun 2008) and end date (28 Jun 2008)

i built it using WHILE statement
so i loop for days between this period and insert them one by one

i dont think this is the best way to do that as if the period is longer this will not be so efficient e.g. for period (1 Jan 1964) to (31 dec 2037)

is there any way i can do it using 1 insert statement
like
insert into XTable
select date
from ....
where date between '1 Jan 1964' and '31 dec 2037'

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-17 : 09:51:05

you can

CREATE TABLE XTable
(
xid int identity(1,1),
xdate datetime
)
GO

DECLARE @StartDate datetime, @EndDate datetime
SELECT  @StartDate='24 Jun 2008',@EndDate='28 Jun 2008'
;With Date_CTE (Date) AS
(SELECT @StartDate
UNION ALL
SELECT DATEADD(d,1,Date)
FROM Date_CTE
WHERE DATEADD(d,1,Date)<=@EndDate)

INSERT INTO XTable(xdate)
SELECT Date FROM Date_CTE

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-17 : 09:54:11

with temp1 as (
select cast('24 Jun 2008' as datetime) as TheDate
UNION ALL
select dateadd(day,1,TheDate) from temp1
where TheDate <= cast('28 Jun 2008' as datetime)
)
select * from temp1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-17 : 09:58:30

quote:
Originally posted by hanbingl

with temp1 as (
select cast('24 Jun 2008' as datetime) as TheDate
UNION ALL
select dateadd(day,1,TheDate) from temp1
where TheDate <= cast('28 Jun 2008' as datetime)
)
select * from temp1

by 3 minutes

nice123ej
Starting Member

48 Posts

Posted - 2008-10-18 : 01:34:36

Sorry, but your solution did not work
i get this error now when i try bigger period (02 Jan 2002) to (28 Jun 2008)

Msg 530, Level 16, State 1, Line 4
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-18 : 02:04:37

quote:
Originally posted by nice123ej

Sorry, but your solution did not work
i get this error now when i try bigger period (02 Jan 2002) to (28 Jun 2008)

Msg 530, Level 16, State 1, Line 4
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

try specifying an explicit recursion level

CREATE TABLE XTable
(
xid int identity(1,1),
xdate datetime
)
GO

DECLARE @StartDate datetime, @EndDate datetime
SELECT @StartDate='24 Jun 2008',@EndDate='28 Jun 2008'
;With Date_CTE (Date) AS
(SELECT @StartDate
UNION ALL
SELECT DATEADD(d,1,Date)
FROM Date_CTE
WHERE DATEADD(d,1,Date)<=@EndDate)

INSERT INTO XTable(xdate)
SELECT Date FROM Date_CTE
OPTION (MAXRECURSION 10000);

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources