Returning Date Range - 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

Transact-SQL (2000)

Returning Date Range

Author

Topic

BigRetina
Posting Yak Master

144 Posts

Posted - 2003-06-15 : 10:19:12

Salute...
How can I return a range of days between two dates in one select statement??
for example
@FromDate = '15/1/2003'
@ToDate = '18/1/2003'

I want to return the following result set :
----------
15/1/2003
16/1/2003
17/1/2003
18/1/2003

Thanks In Advance

macka
Posting Yak Master

162 Posts

Posted - 2003-06-15 : 14:28:45

You could use a tally table to achieve this containing all the dates for the next n years. However you can achieve this in a single SELECT statement. The following code can deal with a date difference of 1000 days.


DECLARE @d1 DATETIME
DECLARE @d2 DATETIME

SET DATEFORMAT DMY
SET @d1 = '15/1/2003'
SET @d2 = '18/1/2003'

SELECT DATEADD(d,units.n + (tens.n * 10) + (hundreds.n * 100),@d1) dte
FROM (SELECT 0 n UNION SELECT 1 n UNION SELECT 2 n UNION SELECT 3 n UNION SELECT 4 n UNION SELECT 5 n UNION SELECT 6 n UNION SELECT 7 n UNION SELECT 8 n UNION SELECT 9 n) units
CROSS JOIN (SELECT 0 n UNION SELECT 1 n UNION SELECT 2 n UNION SELECT 3 n UNION SELECT 4 n UNION SELECT 5 n UNION SELECT 6 n UNION SELECT 7 n UNION SELECT 8 n UNION SELECT 9 n) tens
CROSS JOIN (SELECT 0 n UNION SELECT 1 n UNION SELECT 2 n UNION SELECT 3 n UNION SELECT 4 n UNION SELECT 5 n UNION SELECT 6 n UNION SELECT 7 n UNION SELECT 8 n UNION SELECT 9 n) hundreds
WHERE DATEADD(d,units.n + (tens.n * 10) + (hundreds.n * 100),@d1) BETWEEN @d1 AND @d2

macka.

--
There are only 10 types of people in the world - Those who understand binary, and those who don't.

BigRetina
Posting Yak Master

144 Posts

Posted - 2003-06-16 : 02:03:14

wow!!..
Thanks!!..That was clever!!..but so indirect??..or is it me???...LOL!
Thanks Again!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources