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.

Answer 1

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

Answer 2

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.

Answer 3

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?

Answer 4

Please consider and benchmark an indexed physical table versus on the fly generation.

Using Itzik Ben-Gen's number generator (http://www.projectdmx.com/tsql/tblnumbers.aspx#Row) and date functions:

create function dbo.GetDates(@BeginDate datetime, @EndDate datetime) 
returns table as 
    return with n1 (n) as (select 1 union all select 0)
        , n2 (n) as (select 1 from n1 x cross join n1 y)
        , n4 (n) as (select 1 from n2 x cross join n2 y)
        , n8 (n) as (select 1 from n4 x cross join n4 y)
        , n16 (n) as (select 1 from n8 x cross join n8 y)
        , num(n) as (select row_number() over (order by n) from n16)
    select dateadd(day, n-1, @BeginDate) D
    from num
    where n <=  datediff(day, @BeginDate, @EndDate) + 1
go

select * from dbo.GetDates('1900-01-01', '1900-01-05')

1900-01-01 00:00:00.000
1900-01-02 00:00:00.000
1900-01-03 00:00:00.000
1900-01-04 00:00:00.000
1900-01-05 00:00:00.000

This will handle up to 65,536 dates. And fail silently if you ask for more. (Since you can't raise an error inside a function.) The CTES of nX have 2^X rows in them. If you need more add an n32 CTE that is a cross join of n16.