SQL Function for Date Sequences (Version 2.1)

I believe this is the last version for date sequences function. Thanks to Enigma for some wise ideas (sorry, Enigma that I don't know your name). Thanks anyway. I added my 2 cents also.

So, you'd better see the final result - t-sql user defined function that generates date sequences:

/*
########################################################
#
#   F_TBL_CALENDAR
#
#   version # 2.1
#
#   use: select * from dbo.F_TBL_CALENDAR(2004)
#
########################################################
*/

alter function F_TBL_CALENDAR
(@p_year smallint)
returns @tbl table (cal_date smalldatetime)
as
begin
   
   -- 1 --
   -- get start date
   declare @p_start_date smalldatetime
   select @p_start_date = convert(smalldatetime, 
                          convert(varchar(4), @p_year)
                          + '0101')
   
   -- 2 --
   -- calculate days in the year
   declare @p_count int
   select @p_count = datediff(dd, @p_start_date, 
                     dateadd(yy, 1, @p_start_date)) 
   -- 3 --
   -- generate dates
   insert into @tbl
   select cal_date
   from
   (
      select
         @p_start_date +
         n3.num * 100 +
         n2.num * 10 +
         n1.num as cal_date
      from
      (
         select 0 as num union all
         select 1 union all
         select 2 union all
         select 3 union all
         select 4 union all
         select 5 union all
         select 6 union all
         select 7 union all
         select 8 union all
         select 9
      ) n1,
      (
         select 0 as num union all
         select 1 union all
         select 2 union all
         select 3 union all
         select 4 union all
         select 5 union all
         select 6 union all
         select 7 union all
         select 8 union all
         select 9
      ) n2,
      (
         select 0 as num union all
         select 1 union all
         select 2 union all
         select 3
      ) n3
   where (n3.num * 100 + n2.num * 10 + n1.num) < @p_count 
   ) gencalendar
   return
end

Enjoy

Related Posts:

• MSSQL: Some Undocumented Features
• Generate Calendar Dates. Version 2.0
• My howto on sqljunkies
• How to PIVOT Tables in MSSQL 2000
• QA vs VS2003. Debugging stored procedure.
• Year calendar 2004
• Yukon 2005 and Whidbey 2005
• Optional parameters in T-SQL: COALESCE vs. ISNULL vs. IS NULL OR
• MSSQL Reset Identity: TRUNCATE TABLE vs. DBCC CHECKIDENT RESEED
• Why app roles are bad in MSSQL

This works nicely. I've made my own modification that takes a start date instead of a year so that I can do fiscal years, crop years, etc.

Under that condition (abritrary start date), the new calculation to deal with getting 365/366 days as appropriate fails if the start date is Feb 29, XXXX. Specifically, it returns only 365 days (i.e. stops at Feb 27 of the next year instead of Feb 28).

My fix is to check to see if the passed p_startdate is month 2, day 29 and if so, add 1 to the previously calculated number of days.

Anatoly Lubarsky
Nice ...
But if you knew my name ... I wouldnt remain an Enigma ... would I ;)

Ron Porter
select @p_count = datediff(dd, @p_start_date,
dateadd(yy, 1, @p_start_date))


this will also take care of the Feb 29 issue ...