UFN_CALENDARDATES

Returns a table of dates with a given range
Return

Return Type
table
Parameters

Parameter	Parameter Type	Mode
@FIRSTDATE	date	IN
@LASTDATE	date	IN
@INTERVALTYPE	tinyint	IN
Definition

Copy


CREATE function [dbo].[UFN_CALENDARDATES]
(
  @FIRSTDATE date,
  @LASTDATE date,
  @INTERVALTYPE tinyint
)
returns @DATETABLE table (
  [DATE] date,
  [INTERVAL] integer
)
as
begin

  if @FIRSTDATE <= @LASTDATE
    begin
      with DATESTABLE
      as
      (        
        select @FIRSTDATE as [DATE], 1 INTERVAL
        union all
        select 
            case
                when @INTERVALTYPE=1 then
                    dateadd(dd, INTERVAL, @FIRSTDATE)
                when @INTERVALTYPE=2 then
                    dateadd(ww, INTERVAL, @FIRSTDATE)
                when @INTERVALTYPE=3 then
                    dateadd(mm, INTERVAL, @FIRSTDATE)
                when @INTERVALTYPE=4 then
                    dateadd(yy, INTERVAL, @FIRSTDATE)
                else
                    dateadd(dd, INTERVAL, @FIRSTDATE)
            end,
        INTERVAL + 1 [INTERVAL]        
        from DATESTABLE
        where 
            (
            case
                when @INTERVALTYPE=1 then
                    dateadd(dd, INTERVAL, @FIRSTDATE)
                when @INTERVALTYPE=2 then
                    dateadd(ww, INTERVAL, @FIRSTDATE)
                when @INTERVALTYPE=3 then
                    dateadd(mm, INTERVAL, @FIRSTDATE)
                when @INTERVALTYPE=4 then
                    dateadd(yy, INTERVAL, @FIRSTDATE)
                else
                    dateadd(dd, INTERVAL, @FIRSTDATE)
            end) <= @LASTDATE
      )
      insert into @datetable ([DATE], [INTERVAL])
      select [DATE], [INTERVAL] from DATESTABLE
      option (maxrecursion 0);
    end
  return;
  end