Generating Date Series - 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 2000 Forums

SQL Server Development (2000)

Generating Date Series

Author

Topic

akki
Starting Member

14 Posts

Posted - 2003-08-29 : 06:16:55

How can i generate series of dates between given two dates and insert into a table. Can this be achived without using loops, i mean with something like identity etc ??

Please help !!

--akki.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-08-29 : 06:54:14

Sure.


insert into [a table] (
    datecol )
select
    dateadd(dd,n,<somestartdate>)
from
    <some set of n integers>

You can use a tally table as your set of ints, or you can generate it some other way.

Jay White
{0}

akki
Starting Member

14 Posts

Posted - 2003-08-29 : 07:00:36

Thank you very much for your help.

mhhh..... now, how i can insert n numbers into a table ??

Thanks again.

--akki.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-08-29 : 07:40:10

Well, without having such a table handy, you'd need to use a loop.

I love irony.

CREATE TABLE Numbers(n smallint NOT NULL PRIMARY KEY)
INSERT Numbers(n) VALUES(1)
DECLARE @n smallint
SELECT @n=Max(n) FROM Numbers
WHILE @n<8000 --change this value to match the upper value you want
BEGIN
INSERT Numbers(n) SELECT @n+n FROM Numbers WHERE @n+n<=8000 --see comment above
SELECT @n=Max(n) FROM Numbers
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources