how to use sql to make a list of dates? 1

[karen4201]

Let's say I don't have any date items to select. How could I generate a list of dates? For example, I might want a list of all dates between Jan. 1, 2002 and Mar. 1, 2002.

Could I use something like "getdate()" or a similar date function to do a select?

Thanks in advance.

 

[vongrunt]

Why would ya do that temporarily? Better create permanent table holding all dates from say, Y2000 - Y2010 - and then filter date range with WHERE clause when and where necessary.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[karen4201]

Thanks for the suggestion. I could createa table to lookup the dates, but I'd prefer an approach that generates it on the fly.

Any thoughts?

Thanks.

 

[SQLDenis]

2 ways
1 calendar table
2 number table and dateadd

-- Create out Pivot table ** do this only once-- populate it with 1000 rows
CREATE TABLE NumberPivot (NumberID INT PRIMARY KEY)

DECLARE @intLoopCounter INT
SELECT @intLoopCounter =0

WHILE @intLoopCounter <=1000
BEGIN
INSERT INTO NumberPivot
VALUES (@intLoopCounter)

SELECT @intLoopCounter = @intLoopCounter +1
END
GO



DECLARE @dtmDate DATETIME
SELECT @dtmDate = '2002-01-01 00:00:00.000'
DECLARE @dtmDate2 DATETIME
SELECT @dtmDate2 = '2002-03-01 00:00:00.000'
SELECT DATEADD(dd,numberID,@dtmDate)
FROM dbo.NumberPivot
WHERE DATEADD(dd,numberID,@dtmDate) <= @dtmDate2

rest of this code is here

http://sqlservercode.blogspot.com/2005/09/fast-date-ranges-without-loops-in-sql.html

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Geoka]

Declare @FrDate as datetime,
	@ToDate as DateTime

Set @FrDate='20060301'
Set @ToDate='20060331'

While @FrDate <=@ToDate 
   Begin
	Print @FrDate
	Set @FrDate = DateAdd(Day,1,@FrDate)
   End

 

[bborissov]

W/o loop

CREATE TABLE #Test (Open_Date datetime, range_date datetime, Close_Date datetime)

DECLARE @dBegin datetime, @dEnd datetime

--- Period 
SET @dBegin = '20050401'
SET @dEnd   = '20070414'

-- Single select statement here
select r.range_date,
       (SELECT COUNT(*) FROM #Test WHERE Open_Date <= r.Range_Date AND
               (Close_Date >= r.Range_Date OR Close_Date IS NULL))
from  #Test b
right outer join (
select @dBegin + b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12 range_date
from       (select 0 b1  union select 1    b1)  t1
cross join (select 0 b2  union select 2    b2)  t2
cross join (select 0 b3  union select 4    b3)  t3
cross join (select 0 b4  union select 8    b4)  t4
cross join (select 0 b5  union select 16   b5)  t5
cross join (select 0 b6  union select 32   b6)  t6
cross join (select 0 b7  union select 64   b7)  t7
cross join (select 0 b8  union select 128  b8)  t8
cross join (select 0 b9  union select 256  b9)  t9
cross join (select 0 b10 union select 512  b10) t10
cross join (select 0 b11 union select 1024 b11) t11
cross join (select 0 b12 union select 2048 b12) t12
where @dBegin+b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12 <= @dEnd) r
on b.open_date = r.range_date
order by r.range_date

drop table #test

Borislav Borissov

 

[SQLSister]

Why would you want to generate it on the fly? That would inherently be a less efficient approach. Why put the unnecessary load on the server?

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[karen4201]

ok. now that I've seen the different approaches, I see it's better to not calculate it on the fly. I've used the "pivot" table idea. Thanks