Return temp table of continuous dates
sql, sql-server, sql-server-2005, t-sql
Solution
Your best option is to actually have a physical table of dates. There aren't that many for even long periods, and will be much faster than materializing them on-the-fly from temp tables or recursive ctes.
Problem
I need to create a function that returns a table of continuous dates. I would pass in a min & max date. I expect it to be able to be called like this: ``` SELECT * FROM GetDates('01/01/2009', '12/31/2009') ``` I currently have a stored proc that does this, but requirements changed and now I need to do include the returned data from within a union: ``` with mycte as ( select cast(@minDate as datetime) DateValue union all select DateValue + 1 from mycte where DateValue + 1 <= @maxDate ) select DateValue from mycte option (maxrecursion 1000) ``` The problem, however, is that I need to set the recursion to be greater than 100. According to a post by Gail Erickson [MS] on eggheadcafe, this is not currently supported. Without creating a real (not temporary) table with just date in it, is there a way to do this? I am using SqlServer2005.