Generating a series of numbers or dates?

[Christopher M. Pieper]

I want to generate a resultset that is just a series of numbers in
ascending order or perhaps a series of dates.. What I mean is, is
there a way to generate a temporary table of dates given an input of a
start date and an end date.. This table would then contain an entry
for each date in ascending order from the start date to and including
the end date..

Or perhaps with numbers, given a start of 5 and and end of 7
the resulting table would be

5
6
7

Would appreciate any help with this one.. Thanks
Chris

[David Portas]

The usual technique is just to keep these as permanent tables in your
database and then SELECT numbers and dates out of them as required.

Dates:

CREATE TABLE Calendar
(caldate DATETIME NOT NULL PRIMARY KEY)

INSERT INTO Calendar (caldate) VALUES ('20000101')

WHILE (SELECT MAX(caldate) FROM Calendar)<'20101231'
INSERT INTO Calendar (caldate)
SELECT DATEADD(D,DATEDIFF(D,'19991231',caldate),
(SELECT MAX(caldate) FROM Calendar))
FROM Calendar

Numbers:

CREATE TABLE Numbers
(num INTEGER PRIMARY KEY)

INSERT INTO Numbers VALUES (1)

WHILE (SELECT MAX(num) FROM Numbers)<8192
INSERT INTO Numbers
SELECT num+(SELECT MAX(num) FROM Numbers)
FROM Numbers

--
David Portas
------------
Please reply only to the newsgroup
--

[Anith Sen]

Refer to:
http://www.bizdatasolutions.com/tsql/tblnumbers.asp

--
-- Anith
( Please reply to newsgroups only )