Generate Calendar Dates. Version 2.0
Concerning my howto on sqljunkies I've got a feedback from Thomas Darmstandler. Thanks, Tom. He points out some things I didn't make up to the end. These are:
1) not returning 365 or 366 days for the year 2) although my version #1 takes care of a leap year, you still need to figure out whether to return 365 or 366 days
The following function by Tom returns the days of the year you requested.
alter function fn_CalendarTbl
/************************************************
Function: fn_CalendarTbl
Purpose: Creates an on the fly table for date
searches and joins
Returns: A table of one year of dates
Example Call: select * from fn_CalendarTbl(2003)
select * from fn_CalendarTbl(2004)
Notes: Accounts for leap years
Authors: Anatoly Lubarsky / Thomas Darmstandler
-------- --------- ---- ----------
History: 4/8/2004 Created
************************************************/
(@yr int)
returns @t2 table (dt datetime)
as
begin
declare @startDt datetime,
@dt datetime,
@leapDt datetime,
@retVal int,
@enterDt varchar(20)
declare @t table (dt smalldatetime)
set @startDt = convert(smalldatetime,
convert(varchar(4), @yr) + '0101')
insert into @t
select dt
from
(select
@startDt +
n3.num * 100 +
n2.num * 10 +
n1.num as dt
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) gencalendar
order by 1
---------------
-- accounts for leap years
set @enterDt ='2/28/' + cast(@yr as varchar(4))
set @leapDt = (select dateadd(d,1,@enterDt))
set @retVal = (select datepart(d,@leapDt))
if @retVal = 29
begin
insert into @t2
select top 366 *
from @t
end
else
begin
insert into @t2
select top 365 *
from @t
end
return
end
My response: It works fine, but the perfomance is not good because:
1) performing the whole insert twice 2) extra variables for example: extra table variable, extra datetime variable. In udf you have to be aware of it. 3) datetime conversion formats won't work under certain locals
So I enhanced my version #1 a bit. So you get version #2:
/*
########################################################
#
# F_TBL_CALENDAR
#
# version #2
#
# use: select * from dbo.F_TBL_CALENDAR(2003)
#
########################################################
*/
alter function F_TBL_CALENDAR
(@p_year smallint)
returns @tbl table (cal_date smalldatetime)
as
begin
-- 1 --
-- check for leap year
declare @p_leap_date smalldatetime
declare @p_check_day int
set @p_leap_date = cast(@p_year as varchar(4)) + '0228'
set @p_leap_date = (select dateadd(d, 1, @p_leap_date))
set @p_check_day = (select datepart(d, @p_leap_date))
-- 2 --
-- create start date
declare @p_start_date smalldatetime
set @p_start_date = convert(smalldatetime,
convert(varchar(4), @p_year)
+ '0101')
-- 3 --
-- generate dates
insert into @tbl
select top 365 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
) gencalendar
order by 1
-- 4 --
-- if it was a leap year
-- add 31/12
if (@p_check_day = 29)
insert into @tbl values(convert(smalldatetime,
convert(varchar(4),
@p_year)
+ '1231'))
return
end
:)
Saturday, April 10, 2004 4:14 AM
