Get all dates between date range

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)

Get all dates between date range

Author

Topic

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2006-08-05 : 23:38:28

I'm trying to accomplish the following

I need to get a list of all dates between a startdate and a enddate, where I designate the Incrementation. I can accomplish this with a while statment, but can it be done in a query?

for example:

Create Table #t1(DateCol dateTime)

Declare @AdjustDate datetime, @Startdate Datetime, @EndDate DateTime, @Inc varchar(20)
set @Startdate = '01/01/2005'
set @EndDate = '01/01/2006'
set @Inc = 'Month'
set @Adjustdate = @Startdate
while @Adjustdate <= @EndDate
BEGIN
Insert into #t1(@Adjustdate)
set @Adjustdate = DateAdd(@INC,1,@AdjustDate)
END

This will work, but I'd rather not use a while statment with numourus inserts.

Thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-05 : 23:51:42

You can use a CTE
see the examples in
http://www.simple-talk.com/sql/sql-server-2005/sql-server-2005-common-table-expressions/

or if v2000 a similar thing with a derived table
see the ints table in
http://www.nigelrivett.net/SQLTsql/FindGapsInSequence.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2006-08-06 : 00:32:41

This is the first time I've looked into CTE, how are they on performance compared to useing the while statment?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-06 : 12:03:57

Look at this function [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519[/url]. Or simply

DECLARE	@StartDate DATETIME,
	@EndDate DATETIME

SELECT	@StartDate = '20060101',
	@EndDate = '20060930'

SELECT		DATEADD(day, z.num, @StartDate)
FROM		(
			SELECT		b10.i + b9.i + b8.i + b7.i + b6.i + b5.i + b4.i + b3.i + b2.i + b1.i + b0.i num
			FROM		(SELECT 0 i UNION ALL SELECT 1) b0
			CROSS JOIN	(SELECT 0 i UNION ALL SELECT 2) b1
			CROSS JOIN	(SELECT 0 i UNION ALL SELECT 4) b2
			CROSS JOIN	(SELECT 0 i UNION ALL SELECT 8) b3
			CROSS JOIN	(SELECT 0 i UNION ALL SELECT 16) b4
			CROSS JOIN	(SELECT 0 i UNION ALL SELECT 32) b5
			CROSS JOIN	(SELECT 0 i UNION ALL SELECT 64) b6
			CROSS JOIN	(SELECT 0 i UNION ALL SELECT 128) b7
			CROSS JOIN	(SELECT 0 i UNION ALL SELECT 256) b8
			CROSS JOIN	(SELECT 0 i UNION ALL SELECT 512) b9
			CROSS JOIN	(SELECT 0 i UNION ALL SELECT 1024) b10
		) z
WHERE		z.num <= DATEDIFF(day, @StartDate, @EndDate)
ORDER BY	z.num

Peter Larsson
Helsingborg, Sweden

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2006-08-06 : 13:17:08

I need this to work by specifying the type of increment(Month,day,year,etc). It's a easy enough tweak, but which method is best to use out of the ones listed?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-06 : 13:29:56

Which maximum range do you need?
0-1,000?
0-1,000,000?

Peter Larsson
Helsingborg, Sweden

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-06 : 14:07:01

The CTE should be faster than the while statement and the derived table as well as being simplest to code.
There are methods which are faster but involve a permanent table and some quite complex code.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2006-08-06 : 17:59:01

0-1000 in 99% of the time would be sufficiant. Also nr, in your opinion is Peso's method better then the CTE? It looks as if it would be a better option.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-06 : 18:19:37

It is the method I indicated in the second link I posted.
I should work out slower than the cte and is more coding.
If you don't have to worry about backward compatibility I would go with the CTE.
with t1 (d)
as
(
select d = convert(datetime,'20050101')
union all
select d = d+1 from t1 where d < '20060101'
)
select * from t1 order by d
option (maxrecursion 370)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources