How to Generate Dates in SQL Server

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 2005 Forums

Transact-SQL (2005)

How to Generate Dates in SQL Server

Author

Topic

TMDOSS
Starting Member

33 Posts

Posted - 2008-09-09 : 09:41:48

I need to Generate dates from user inputdate1 to user input date2
EX: all the dates between '2008-01-01' to '2009-01-01'
I tried to use the Following Stored procedure

with mycte as
(
select @FromDate DateValue
union all
select DateValue + 1
from mycte
where DateValue + 1 < @todate

)
select DateValue
from mycte

It works it the date range is with in the Limit of 100.

Please advice

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-09 : 09:42:57

You can write

select DateValue
from mycte
option (maxrecursion 0)

to get all dates. This is documented in Books Online.

Or simply use the F_TABLE_DATE function provided by MVJ here at SQLTeam.

E 12°55'05.63"
N 56°04'39.26"

TMDOSS
Starting Member

33 Posts

Posted - 2008-09-09 : 09:48:41

Cool. It's Great. It works. You guys Rock

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources