Tutorial :Return temp table of continuous dates



Question:

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.


Solution:1

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.


Solution:2

If you choose to (or need to) go with an ad-hoc table and not a permanent one, this would do it:

CREATE FUNCTION dbo.DateList   (     @MinDate datetime    ,@MaxDate datetime   )  RETURNS TABLE  RETURN WITH    Pass0 as (select 1 as C union all select 1), --2 rows    Pass1 as (select 1 as C from Pass0 as A, Pass0 as B),--4 rows    Pass2 as (select 1 as C from Pass1 as A, Pass1 as B),--16 rows    Pass3 as (select 1 as C from Pass2 as A, Pass2 as B),--256 rows    Pass4 as (select 1 as C from Pass3 as A, Pass3 as B),--65536 rows    Tally as (select row_number() over(order by C) as Number from Pass4)   select dateadd(dd, Number - 1, @MinDate) DateValue   from Tally   where Number < datediff(dd, @MindAte, @MaxDate) + 2  

GO

And a testing call:

DECLARE    @MinDate datetime   ,@MaxDate datetime    SET @MinDate = 'Jan 1, 2009'  SET @MaxDate = 'Dec 31, 2009'    SELECT *   from dbo.DateList(@MinDate, @MaxDate)  

Wierd--this is the third SO post today that involved Tally tables. Must be some odd sunspot activity going on. Here are the linkes:

count number of rows that occur for each date in column date range.
What is the best way to create and populate a numbers table?


Solution:3

something like this:

CREATE FUNCTION GetDates(@StartDate DateTime, @EndDate DateTime)     RETURNS @Dates Table ( aDate DateTime Primary Key Not Null)  AS  BEGIN   Declare @ThisDate DateTime Set @ThisDate = @StartDate     While @ThisDate < @EndDate begin              Insert @Dates (aDate) Values(@THisDate)              Set @ThisDate = @ThisDate + 1     End  RETURN   END  GO  

make sure @EndDate is after @startdate... Add input parameter checking to makes sure, or it could run forever if you pass it dates backwards


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »