Anatoly Lubarsky Logo
programming, design, integration, games, music

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:

Thursday, April 15, 2004 2:43 AM

Comments

# re: Year Dates UDF. Version #2.1.
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.

4/15/2004 8:40 PM by Ron Porter

# re: Year Dates UDF. Version #2.1.
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 ...

4/19/2004 7:33 PM by Enigma

Login

Subscribe via RSS

Article Categories

.Net Framework
ASP.NET Tips
C# Win32 API
HTML, CSS, Web
Javascript Tips
MSSQL Tips
System
System.Net
WebServices

Archives

(02) January, 2018
(01) June, 2013
(03) March, 2013
(02) February, 2013
(01) July, 2012
(01) April, 2012
(01) September, 2011
(01) August, 2011
(03) May, 2011
(01) March, 2011
(02) December, 2010
(01) November, 2010
(01) October, 2010
(01) June, 2010
(01) May, 2010
(02) March, 2010
(01) January, 2010
(02) December, 2009
(03) September, 2009
(03) August, 2009
(09) July, 2009
(04) June, 2009
(03) May, 2009
(02) April, 2009
(03) March, 2009
(02) February, 2009
(02) January, 2009
(05) December, 2008
(04) November, 2008
(06) October, 2008
(04) September, 2008
(07) August, 2008
(04) July, 2008
(05) June, 2008
(07) May, 2008
(05) April, 2008
(03) March, 2008
(02) February, 2008
(03) January, 2008
(03) December, 2007
(05) November, 2007
(05) October, 2007
(07) September, 2007
(13) August, 2007
(11) July, 2007
(14) June, 2007
(16) May, 2007
(14) April, 2007
(10) March, 2007
(14) February, 2007
(20) January, 2007
(15) December, 2006
(12) November, 2006
(11) October, 2006
(13) September, 2006
(13) August, 2006
(12) July, 2006
(11) June, 2006
(12) May, 2006
(22) April, 2006
(30) March, 2006
(13) February, 2006
(14) January, 2006
(19) December, 2005
(17) November, 2005
(17) October, 2005
(17) September, 2005
(12) August, 2005
(14) July, 2005
(09) June, 2005
(15) May, 2005
(12) April, 2005
(21) March, 2005
(11) February, 2005
(12) January, 2005
(19) December, 2004
(13) November, 2004
(12) October, 2004
(15) September, 2004
(09) August, 2004
(25) July, 2004
(23) June, 2004
(31) May, 2004
(21) April, 2004
(16) March, 2004
(09) February, 2004
(06) January, 2004
(02) December, 2003
(01) November, 2003

Post Categories

.Net and C#
Android
Antispam
App. Development
Architecture
ASP.NET
Blogging
Deprecated Projects
Facebook Platform
Fun
Google
iOS
Javascript
Misc.
MSSQL
Music
My Games
Performance
Roller
Social Networks
Tools
Visual Studio
Web 2.0
WebServices

About Me

linkedin Profile
Recs
Who am I

My Sites

Billy Beet
x2line blogs