Geek Answers Handbook

Continuous Date Temporary Return Table

I need to create a function that returns a table of continuous dates. I would have gone in a minute and a maximum.

I expect him to be able to be called that:

SELECT * FROM GetDates('01/01/2009', '12/31/2009')

I currently have a stored process that does this, but the requirements are changing, and now I need to include the returned data from the 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 more than 100. According to a post by Gail Erickson [MS] on eggheadcafe , this is not currently supported.

Without creating a real (non-temporary) table with only the date in it, is there a way to do this?

I am using SqlServer2005.

+5
3

- . , , ctes.

+6

( ) , , :

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

:

DECLARE
  @MinDate datetime
 ,@MaxDate datetime

SET @MinDate = 'Jan 1, 2009'
SET @MaxDate = 'Dec 31, 2009'

SELECT *
 from dbo.DateList(@MinDate, @MaxDate)

Wierd - SO, Tally. - . :

, .
?

+3

- :

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 after @startdate ... Add an input parameter check to make sure it can work forever if you pass it back dates

+1
source

More articles:


All Articles