Generate date range T-SQL


I came across this script from one blog to replace a calendar table:

;with mycte as

(

select cast(‘2001-01-01’ as datetime) DateValue

union all

select DateValue + 1 from mycte

where DateValue + 1 < ‘2010-3-31’

)

select

* from mycte

http://blogs.conchango.com/jamiethomson/archive/2007/01/11/T_2D00_SQL_3A00_-Generate-a-list-of-dates.aspx

I used this script to answer a question here:

http://forums.asp.net/t/1297396.aspx

;with mycte as

(

select cast(‘2000-01-01’ as datetime) DateValue

union all

select DateValue + 1 from mycte

where DateValue + 1 < ‘2010-12-31’

)

SELECT

DatesRange.Name, mycte.dateValue

FROM

mycte CROSS JOIN

DatesRange

WHERE mycte.dateValue BETWEEN DatesRange.FromDT AND DatesRange.ToDT

OPTION (MAXRECURSION 0)

 

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s