Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-02-09 : 21:48:05
|
Function F_TABLE_DATE is a multistatement table-valued function that returns a table containing a variety of attributes of all dates from @FIRST_DATE through @LAST_DATE. In short, it’s a calendar table function.
This just kept growing as I adding more and more items. I think this is finished enough to share, since it is up to around 65 columns.
The general operation of the function and the contents of the output columns are documented in the code. If you any questions, post them here, and I'll respond.
If you spot any problems or bugs or have any suggestions, I would appreciate any feedback.
Edit: Modified 2006-07-07 to support systems running a non-US English DATEFORMAT. Also added a checksum test to the script to confirm that results are the same for different date formats. This change does not convert the various output formats to local formats; it only makes the function produce the same output no matter what the DATEFORMAT is.
If you have the earlier version, you may want to get this updated code.
Edit (2012-07-05): Corrected error in contents of column DATE_FORMAT_YYYY_M_D. It was returning the date in format YYYY/D/M, instead of YYYY/M/D. Error was reported by MorrisK in post on this thread on 02/08/2012 15:40:08, but I just noticed his post today.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[F_TABLE_DATE]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[F_TABLE_DATE] GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO create function dbo.F_TABLE_DATE ( @FIRST_DATE datetime, @LAST_DATE datetime ) /* Function: dbo.F_TABLE_DATE
This function returns a date table containing all dates from @FIRST_DATE through @LAST_DATE inclusive. @FIRST_DATE must be less than or equal to @LAST_DATE. The valid date range is 1754-01-01 through 9997-12-31. If any input parameters are invalid, the fuction will produce an error.
The table returned by F_TABLE_DATE contains a date and columns with many calculated attributes of that date. It is designed to make it convenient to get various commonly needed date attributes without having to program and test the same logic in many applications.
F_TABLE_DATE is primarily intended to load a permanant date table, but it can be used directly by an application when the date range needed falls outside the range loaded in a permanant table.
If F_TABLE_DATE is used to load a permanant table, the create table code can be copied from this function. For a permanent date table, most columns should be indexed to produce the best application performance.
Column Descriptions ------------------------------------------------------------------
DATE_ID Unique ID = Days since 1753-01-01
DATE Date at Midnight(00:00:00.000)
NEXT_DAY_DATE Next day after DATE at Midnight(00:00:00.000) Intended to be used in queries against columns containing datetime values (1998-12-13 14:35:16) that need to join to a DATE. Example:
from MyTable a join DATE b on a.DateTimeCol >= b. DATE and a.DateTimeCol < b.NEXT_DAY_DATE
YEAR Year number in format YYYY, Example = 2005
YEAR_QUARTER Year and Quarter number in format YYYYQ, Example = 20052
YEAR_MONTH Year and Month number in format YYYYMM, Example = 200511
YEAR_DAY_OF_YEAR Year and Day of Year number in format YYYYDDD, Example = 2005364
QUARTER Quarter number in format Q, Example = 4
MONTH Month number in format MM, Example = 11
DAY_OF_YEAR Day of Year number in format DDD, Example = 362
DAY_OF_MONTH Day of Month number in format DD, Example = 31
DAY_OF_WEEK Day of week number, Sun=1, Mon=2, Tue=3, Wed=4, Thu=5, Fri=6, Sat=7
YEAR_NAME Year name text in format YYYY, Example = 2005
YEAR_QUARTER_NAME Year Quarter name text in format YYYY QQ, Example = 2005 Q3
YEAR_MONTH_NAME Year Month name text in format YYYY MMM, Example = 2005 Mar
YEAR_MONTH_NAME_LONG Year Month long name text in format YYYY MMMMMMMMM, Example = 2005 September
QUARTER_NAME Quarter name text in format QQ, Example = Q1
MONTH_NAME Month name text in format MMM, Example = Mar
MONTH_NAME_LONG Month long name text in format MMMMMMMMM, Example = September
WEEKDAY_NAME Weekday name text in format DDD, Example = Tue
WEEKDAY_NAME_LONG Weekday long name text in format DDDDDDDDD, Example = Wednesday
START_OF_YEAR_DATE First Day of Year that DATE is in
END_OF_YEAR_DATE Last Day of Year that DATE is in
START_OF_QUARTER_DATE First Day of Quarter that DATE is in
END_OF_QUARTER_DATE Last Day of Quarter that DATE is in
START_OF_MONTH_DATE First Day of Month that DATE is in
END_OF_MONTH_DATE Last Day of Month that DATE is in
*** Start and End of week columns allow selections by week *** for any week start date needed.
START_OF_WEEK_STARTING_SUN_DATE First Day of Week starting Sunday that DATE is in
END_OF_WEEK_STARTING_SUN_DATE Last Day of Week starting Sunday that DATE is in
START_OF_WEEK_STARTING_MON_DATE First Day of Week starting Monday that DATE is in
END_OF_WEEK_STARTING_MON_DATE Last Day of Week starting Monday that DATE is in
START_OF_WEEK_STARTING_TUE_DATE First Day of Week starting Tuesday that DATE is in
END_OF_WEEK_STARTING_TUE_DATE Last Day of Week starting Tuesday that DATE is in
START_OF_WEEK_STARTING_WED_DATE First Day of Week starting Wednesday that DATE is in
END_OF_WEEK_STARTING_WED_DATE Last Day of Week starting Wednesday that DATE is in
START_OF_WEEK_STARTING_THU_DATE First Day of Week starting Thursday that DATE is in
END_OF_WEEK_STARTING_THU_DATE Last Day of Week starting Thursday that DATE is in
START_OF_WEEK_STARTING_FRI_DATE First Day of Week starting Friday that DATE is in
END_OF_WEEK_STARTING_FRI_DATE Last Day of Week starting Friday that DATE is in
START_OF_WEEK_STARTING_SAT_DATE First Day of Week starting Saturday that DATE is in
END_OF_WEEK_STARTING_SAT_DATE Last Day of Week starting Saturday that DATE is in
*** Sequence No columns are intended to allow easy offsets by *** Quarter, Month, or Week for applications that need to look at *** Last or Next Quarter, Month, or Week. Thay can also be used to *** generate dynamic cross tab results by Quarter, Month, or Week.
QUARTER_SEQ_NO Sequential Quarter number as offset from Quarter starting 1753/01/01
MONTH_SEQ_NO Sequential Month number as offset from Month starting 1753/01/01
WEEK_STARTING_SUN_SEQ_NO Sequential Week number as offset from Week starting Sunday, 1753/01/07
WEEK_STARTING_MON_SEQ_NO Sequential Week number as offset from Week starting Monday, 1753/01/01
WEEK_STARTING_TUE_SEQ_NO Sequential Week number as offset from Week starting Tuesday, 1753/01/02
WEEK_STARTING_WED_SEQ_NO Sequential Week number as offset from Week starting Wednesday, 1753/01/03
WEEK_STARTING_THU_SEQ_NO Sequential Week number as offset from Week starting Thursday, 1753/01/04
WEEK_STARTING_FRI_SEQ_NO Sequential Week number as offset from Week starting Friday, 1753/01/05
WEEK_STARTING_SAT_SEQ_NO Sequential Week number as offset from Week starting Saturday, 1753/01/06
JULIAN_DATE Julian Date number as offset from noon on January 1, 4713 BCE to noon on day of DATE in system of Joseph Scaliger
MODIFIED_JULIAN_DATE Modified Julian Date number as offset from midnight(00:00:00.000) on 1858/11/17 to midnight(00:00:00.000) on day of DATE
ISO_DATE ISO 8601 Date in format YYYY-MM-DD, Example = 2004-02-29
ISO_YEAR_WEEK_NO ISO 8601 year and week in format YYYYWW, Example = 200403
ISO_WEEK_NO ISO 8601 week of year in format WW, Example = 52
ISO_DAY_OF_WEEK ISO 8601 Day of week number, Mon=1, Tue=2, Wed=3, Thu=4, Fri=5, Sat=6, Sun=7
ISO_YEAR_WEEK_NAME ISO 8601 year and week in format YYYY-WNN, Example = 2004-W52
ISO_YEAR_WEEK_DAY_OF_WEEK_NAME ISO 8601 year, week, and day of week in format YYYY-WNN-D, Example = 2004-W52-2
DATE_FORMAT_YYYY_MM_DD Text date in format YYYY/MM/DD, Example = 2004/02/29
DATE_FORMAT_YYYY_M_D Text date in format YYYY/M/D, Example = 2004/2/9
DATE_FORMAT_MM_DD_YYYY Text date in format MM/DD/YYYY, Example = 06/05/2004
DATE_FORMAT_M_D_YYYY Text date in format M/D/YYYY, Example = 6/5/2004
DATE_FORMAT_MMM_D_YYYY Text date in format MMM D, YYYY, Example = Jan 4, 2006
DATE_FORMAT_MMMMMMMMM_D_YYYY Text date in format MMMMMMMMM D, YYYY, Example = September 3, 2004
DATE_FORMAT_MM_DD_YY Text date in format MM/DD/YY, Example = 06/05/97
DATE_FORMAT_M_D_YY Text date in format M/D/YY, Example = 6/5/97
*/
returns @DATE table ( [DATE_ID] [int] not null primary key clustered, [DATE] [datetime] not null , [NEXT_DAY_DATE] [datetime] not null , [YEAR] [smallint] not null , [YEAR_QUARTER] [int] not null , [YEAR_MONTH] [int] not null , [YEAR_DAY_OF_YEAR] [int] not null , [QUARTER] [tinyint] not null , [MONTH] [tinyint] not null , [DAY_OF_YEAR] [smallint] not null , [DAY_OF_MONTH] [smallint] not null , [DAY_OF_WEEK] [tinyint] not null ,
[YEAR_NAME] [varchar] (4) not null , [YEAR_QUARTER_NAME] [varchar] (7) not null , [YEAR_MONTH_NAME] [varchar] (8) not null , [YEAR_MONTH_NAME_LONG] [varchar] (14) not null , [QUARTER_NAME] [varchar] (2) not null , [MONTH_NAME] [varchar] (3) not null , [MONTH_NAME_LONG] [varchar] (9) not null , [WEEKDAY_NAME] [varchar] (3) not null , [WEEKDAY_NAME_LONG] [varchar] (9) not null ,
[START_OF_YEAR_DATE] [datetime] not null , [END_OF_YEAR_DATE] [datetime] not null , [START_OF_QUARTER_DATE] [datetime] not null , [END_OF_QUARTER_DATE] [datetime] not null , [START_OF_MONTH_DATE] [datetime] not null , [END_OF_MONTH_DATE] [datetime] not null ,
[START_OF_WEEK_STARTING_SUN_DATE] [datetime] not null , [END_OF_WEEK_STARTING_SUN_DATE] [datetime] not null , [START_OF_WEEK_STARTING_MON_DATE] [datetime] not null , [END_OF_WEEK_STARTING_MON_DATE] [datetime] not null , [START_OF_WEEK_STARTING_TUE_DATE] [datetime] not null , [END_OF_WEEK_STARTING_TUE_DATE] [datetime] not null , [START_OF_WEEK_STARTING_WED_DATE] [datetime] not null , [END_OF_WEEK_STARTING_WED_DATE] [datetime] not null , [START_OF_WEEK_STARTING_THU_DATE] [datetime] not null , [END_OF_WEEK_STARTING_THU_DATE] [datetime] not null , [START_OF_WEEK_STARTING_FRI_DATE] [datetime] not null , [END_OF_WEEK_STARTING_FRI_DATE] [datetime] not null , [START_OF_WEEK_STARTING_SAT_DATE] [datetime] not null , [END_OF_WEEK_STARTING_SAT_DATE] [datetime] not null ,
[QUARTER_SEQ_NO] [int] not null , [MONTH_SEQ_NO] [int] not null ,
[WEEK_STARTING_SUN_SEQ_NO] [int] not null , [WEEK_STARTING_MON_SEQ_NO] [int] not null , [WEEK_STARTING_TUE_SEQ_NO] [int] not null , [WEEK_STARTING_WED_SEQ_NO] [int] not null , [WEEK_STARTING_THU_SEQ_NO] [int] not null , [WEEK_STARTING_FRI_SEQ_NO] [int] not null , [WEEK_STARTING_SAT_SEQ_NO] [int] not null ,
[JULIAN_DATE] [int] not null , [MODIFIED_JULIAN_DATE] [int] not null ,
[ISO_DATE] [varchar](10) not null , [ISO_YEAR_WEEK_NO] [int] not null , [ISO_WEEK_NO] [smallint] not null , [ISO_DAY_OF_WEEK] [tinyint] not null , [ISO_YEAR_WEEK_NAME] [varchar](8) not null , [ISO_YEAR_WEEK_DAY_OF_WEEK_NAME] [varchar](10) not null ,
[DATE_FORMAT_YYYY_MM_DD] [varchar](10) not null , [DATE_FORMAT_YYYY_M_D] [varchar](10) not null , [DATE_FORMAT_MM_DD_YYYY] [varchar](10) not null , [DATE_FORMAT_M_D_YYYY] [varchar](10) not null , [DATE_FORMAT_MMM_D_YYYY] [varchar](12) not null , [DATE_FORMAT_MMMMMMMMM_D_YYYY] [varchar](18) not null , [DATE_FORMAT_MM_DD_YY] [varchar](8) not null , [DATE_FORMAT_M_D_YY] [varchar](8) not null ) as begin declare @cr varchar(2) select @cr = char(13)+Char(10) declare @ErrorMessage varchar(400) declare @START_DATE datetime declare @END_DATE datetime declare @LOW_DATE datetime
declare @start_no int declare @end_no int
-- Verify @FIRST_DATE is not null if @FIRST_DATE is null begin select @ErrorMessage = '@FIRST_DATE cannot be null' goto Error_Exit end
-- Verify @LAST_DATE is not null if @LAST_DATE is null begin select @ErrorMessage = '@LAST_DATE cannot be null' goto Error_Exit end
-- Verify @FIRST_DATE is not before 1754-01-01 IF @FIRST_DATE < '17540101' begin select @ErrorMessage = '@FIRST_DATE cannot before 1754-01-01'+ ', @FIRST_DATE = '+ isnull(convert(varchar(40),@FIRST_DATE,121),'NULL') goto Error_Exit end
-- Verify @LAST_DATE is not after 9997-12-31 IF @LAST_DATE > '99971231' begin select @ErrorMessage = '@LAST_DATE cannot be after 9997-12-31'+ ', @LAST_DATE = '+ isnull(convert(varchar(40),@LAST_DATE,121),'NULL') goto Error_Exit end
-- Verify @FIRST_DATE is not after @LAST_DATE if @FIRST_DATE > @LAST_DATE begin select @ErrorMessage = '@FIRST_DATE cannot be after @LAST_DATE'+ ', @FIRST_DATE = '+ isnull(convert(varchar(40),@FIRST_DATE,121),'NULL')+ ', @LAST_DATE = '+ isnull(convert(varchar(40),@LAST_DATE,121),'NULL') goto Error_Exit end
-- Set @START_DATE = @FIRST_DATE at midnight select @START_DATE = dateadd(dd,datediff(dd,0,@FIRST_DATE),0) -- Set @END_DATE = @LAST_DATE at midnight select @END_DATE = dateadd(dd,datediff(dd,0,@LAST_DATE),0) -- Set @LOW_DATE = earliest possible SQL Server datetime select @LOW_DATE = convert(datetime,'17530101')
-- Find the number of day from 1753-01-01 to @START_DATE and @END_DATE select @start_no = datediff(dd,@LOW_DATE,@START_DATE) , @end_no = datediff(dd,@LOW_DATE,@END_DATE)
-- Declare number tables declare @num1 table (NUMBER int not null primary key clustered) declare @num2 table (NUMBER int not null primary key clustered) declare @num3 table (NUMBER int not null primary key clustered)
-- Declare table of ISO Week ranges declare @ISO_WEEK table ( [ISO_WEEK_YEAR] int not null primary key clustered, [ISO_WEEK_YEAR_START_DATE] datetime not null, [ISO_WEEK_YEAR_END_DATE] Datetime not null )
-- Find rows needed in number tables declare @rows_needed int declare @rows_needed_root int select @rows_needed = @end_no - @start_no + 1 select @rows_needed = case when @rows_needed < 10 then 10 else @rows_needed end select @rows_needed_root = convert(int,ceiling(sqrt(@rows_needed)))
-- Load number 0 to 16 insert into @num1 (NUMBER) select NUMBER = 0 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 union all select 10 union all select 11 union all select 12 union all select 13 union all select 14 union all select 15 order by 1 -- Load table with numbers zero thru square root of the number of rows needed +1 insert into @num2 (NUMBER) select NUMBER = a.NUMBER+(16*b.NUMBER)+(256*c.NUMBER) from @num1 a cross join @num1 b cross join @num1 c where a.NUMBER+(16*b.NUMBER)+(256*c.NUMBER) < @rows_needed_root order by 1
-- Load table with the number of rows needed for the date range insert into @num3 (NUMBER) select NUMBER = a.NUMBER+(@rows_needed_root*b.NUMBER) from @num2 a cross join @num2 b where a.NUMBER+(@rows_needed_root*b.NUMBER) < @rows_needed order by 1
declare @iso_start_year int declare @iso_end_year int
select @iso_start_year = datepart(year,dateadd(year,-1,@start_date)) select @iso_end_year = datepart(year,dateadd(year,1,@end_date))
-- Load table with start and end dates for ISO week years insert into @ISO_WEEK ( [ISO_WEEK_YEAR], [ISO_WEEK_YEAR_START_DATE], [ISO_WEEK_YEAR_END_DATE] ) select [ISO_WEEK_YEAR] = a.NUMBER, [0ISO_WEEK_YEAR_START_DATE] = dateadd(dd,(datediff(dd,@LOW_DATE, dateadd(day,3,dateadd(year,a.[NUMBER]-1900,0)) )/7)*7,@LOW_DATE), [ISO_WEEK_YEAR_END_DATE] = dateadd(dd,-1,dateadd(dd,(datediff(dd,@LOW_DATE, dateadd(day,3,dateadd(year,a.[NUMBER]+1-1900,0)) )/7)*7,@LOW_DATE)) from ( select NUMBER = NUMBER+@iso_start_year from @num3 where NUMBER+@iso_start_year <= @iso_end_year ) a order by a.NUMBER
-- Load Date table insert into @DATE select [DATE_ID] = a.[DATE_ID] , [DATE] = a.[DATE] ,
[NEXT_DAY_DATE] = dateadd(day,1,a.[DATE]) ,
[YEAR] = datepart(year,a.[DATE]) , [YEAR_QUARTER] = (10*datepart(year,a.[DATE]))+datepart(quarter,a.[DATE]) ,
[YEAR_MONTH] = (100*datepart(year,a.[DATE]))+datepart(month,a.[DATE]) , [YEAR_DAY_OF_YEAR] = (1000*datepart(year,a.[DATE]))+ datediff(dd,dateadd(yy,datediff(yy,0,a.[DATE]),0),a.[DATE])+1 , [QUARTER] = datepart(quarter,a.[DATE]) , [MONTH] = datepart(month,a.[DATE]) , [DAY_OF_YEAR] = datediff(dd,dateadd(yy,datediff(yy,0,a.[DATE]),0),a.[DATE])+1 , [DAY_OF_MONTH] = datepart(day,a.[DATE]) , [DAY_OF_WEEK] = -- Sunday = 1, Monday = 2, ,,,Saturday = 7 (datediff(dd,'17530107',a.[DATE])%7)+1 , [YEAR_NAME] = datename(year,a.[DATE]) , [YEAR_QUARTER_NAME] = datename(year,a.[DATE])+' Q'+datename(quarter,a.[DATE]) , [YEAR_MONTH_NAME] = datename(year,a.[DATE])+' '+left(datename(month,a.[DATE]),3) , [YEAR_MONTH_NAME_LONG] = datename(year,a.[DATE])+' '+datename(month,a.[DATE]) , [QUARTER_NAME] = 'Q'+datename(quarter,a.[DATE]) , [MONTH_NAME] = left(datename(month,a.[DATE]),3) , [MONTH_NAME_LONG] = datename(month,a.[DATE]) , [WEEKDAY_NAME] = left(datename(weekday,a.[DATE]),3) , [WEEKDAY_NAME_LONG] = datename(weekday,a.[DATE]),
[START_OF_YEAR_DATE] = dateadd(year,datediff(year,0,a.[DATE]),0) , [END_OF_YEAR_DATE] = dateadd(day,-1,dateadd(year,datediff(year,0,a.[DATE])+1,0)) ,
[START_OF_QUARTER_DATE] = dateadd(quarter,datediff(quarter,0,a.[DATE]),0) , [END_OF_QUARTER_DATE] = dateadd(day,-1,dateadd(quarter,datediff(quarter,0,a.[DATE])+1,0)) ,
[START_OF_MONTH_DATE] = dateadd(month,datediff(month,0,a.[DATE]),0) , [END_OF_MONTH_DATE] = dateadd(day,-1,dateadd(month,datediff(month,0,a.[DATE])+1,0)),
[START_OF_WEEK_STARTING_SUN_DATE] = dateadd(dd,(datediff(dd,'17530107',a.[DATE])/7)*7,'17530107'), [END_OF_WEEK_STARTING_SUN_DATE] = dateadd(dd,((datediff(dd,'17530107',a.[DATE])/7)*7)+6,'17530107'),
[START_OF_WEEK_STARTING_MON_DATE] = dateadd(dd,(datediff(dd,'17530101',a.[DATE])/7)*7,'17530101'), [END_OF_WEEK_STARTING_MON_DATE] = dateadd(dd,((datediff(dd,'17530101',a.[DATE])/7)*7)+6,'17530101'),
[START_OF_WEEK_STARTING_TUE_DATE] = dateadd(dd,(datediff(dd,'17530102',a.[DATE])/7)*7,'17530102'), [END_OF_WEEK_STARTING_TUE_DATE] = dateadd(dd,((datediff(dd,'17530102',a.[DATE])/7)*7)+6,'17530102'),
[START_OF_WEEK_STARTING_WED_DATE] = dateadd(dd,(datediff(dd,'17530103',a.[DATE])/7)*7,'17530103'), [END_OF_WEEK_STARTING_WED_DATE] = dateadd(dd,((datediff(dd,'17530103',a.[DATE])/7)*7)+6,'17530103'),
[START_OF_WEEK_STARTING_THU_DATE] = dateadd(dd,(datediff(dd,'17530104',a.[DATE])/7)*7,'17530104'), [END_OF_WEEK_STARTING_THU_DATE] = dateadd(dd,((datediff(dd,'17530104',a.[DATE])/7)*7)+6,'17530104'),
[START_OF_WEEK_STARTING_FRI_DATE] = dateadd(dd,(datediff(dd,'17530105',a.[DATE])/7)*7,'17530105'), [END_OF_WEEK_STARTING_FRI_DATE] = dateadd(dd,((datediff(dd,'17530105',a.[DATE])/7)*7)+6,'17530105'),
[START_OF_WEEK_STARTING_SAT_DATE] = dateadd(dd,(datediff(dd,'17530106',a.[DATE])/7)*7,'17530106'), [END_OF_WEEK_STARTING_SAT_DATE] = dateadd(dd,((datediff(dd,'17530106',a.[DATE])/7)*7)+6,'17530106'),
[QUARTER_SEQ_NO] = datediff(quarter,@LOW_DATE,a.[DATE]), [MONTH_SEQ_NO] = datediff(month,@LOW_DATE,a.[DATE]),
[WEEK_STARTING_SUN_SEQ_NO] = datediff(day,'17530107',a.[DATE])/7, [WEEK_STARTING_MON_SEQ_NO] = datediff(day,'17530101',a.[DATE])/7, [WEEK_STARTING_TUE_SEQ_NO] = datediff(day,'17530102',a.[DATE])/7, [WEEK_STARTING_WED_SEQ_NO] = datediff(day,'17530103',a.[DATE])/7, [WEEK_STARTING_THU_SEQ_NO] = datediff(day,'17530104',a.[DATE])/7, [WEEK_STARTING_FRI_SEQ_NO] = datediff(day,'17530105',a.[DATE])/7, [WEEK_STARTING_SAT_SEQ_NO] = datediff(day,'17530106',a.[DATE])/7,
[JULIAN_DATE] = datediff(day,@LOW_DATE,a.[DATE])+2361331, [MODIFIED_JULIAN_DATE] = datediff(day,'18581117',a.[DATE]), --/*
[ISO_DATE] = replace(convert(char(10),a.[DATE],111),'/','-') ,
[ISO_YEAR_WEEK_NO] = (100*b.[ISO_WEEK_YEAR])+ (datediff(dd,b.[ISO_WEEK_YEAR_START_DATE],a.[DATE])/7)+1 ,
[ISO_WEEK_NO] = (datediff(dd,b.[ISO_WEEK_YEAR_START_DATE],a.[DATE])/7)+1 ,
[ISO_DAY_OF_WEEK] = -- Sunday = 1, Monday = 2, ,,,Saturday = 7 (datediff(dd,@LOW_DATE,a.[DATE])%7)+1 ,
[ISO_YEAR_WEEK_NAME] = convert(varchar(4),b.[ISO_WEEK_YEAR])+'-W'+ right('00'+convert(varchar(2),(datediff(dd,b.[ISO_WEEK_YEAR_START_DATE],a.[DATE])/7)+1),2) ,
[ISO_YEAR_WEEK_DAY_OF_WEEK_NAME] = convert(varchar(4),b.[ISO_WEEK_YEAR])+'-W'+ right('00'+convert(varchar(2),(datediff(dd,b.[ISO_WEEK_YEAR_START_DATE],a.[DATE])/7)+1),2) + '-'+convert(varchar(1),(datediff(dd,@LOW_DATE,a.[DATE])%7)+1) , --*/ [DATE_FORMAT_YYYY_MM_DD] = convert(char(10),a.[DATE],111) , [DATE_FORMAT_YYYY_M_D] = convert(varchar(10), convert(varchar(4),year(a.[DATE]))+'/'+ convert(varchar(2),month(a.[DATE]))+'/'+ convert(varchar(2),day(a.[DATE]))), [DATE_FORMAT_MM_DD_YYYY] = convert(char(10),a.[DATE],101) , [DATE_FORMAT_M_D_YYYY] = convert(varchar(10), convert(varchar(2),month(a.[DATE]))+'/'+ convert(varchar(2),day(a.[DATE]))+'/'+ convert(varchar(4),year(a.[DATE]))), [DATE_FORMAT_MMM_D_YYYY] = convert(varchar(12), left(datename(month,a.[DATE]),3)+' '+ convert(varchar(2),day(a.[DATE]))+', '+ convert(varchar(4),year(a.[DATE]))), [DATE_FORMAT_MMMMMMMMM_D_YYYY] = convert(varchar(18), datename(month,a.[DATE])+' '+ convert(varchar(2),day(a.[DATE]))+', '+ convert(varchar(4),year(a.[DATE]))), [DATE_FORMAT_MM_DD_YY] = convert(char(8),a.[DATE],1) , [DATE_FORMAT_M_D_YY] = convert(varchar(8), convert(varchar(2),month(a.[DATE]))+'/'+ convert(varchar(2),day(a.[DATE]))+'/'+ right(convert(varchar(4),year(a.[DATE])),2)) from ( -- Derived table is all dates needed for date range select top 100 percent [DATE_ID] = aa.[NUMBER], [DATE] = dateadd(dd,aa.[NUMBER],@LOW_DATE) from ( select NUMBER = NUMBER+@start_no from @num3 where NUMBER+@start_no <= @end_no ) aa order by aa.[NUMBER] ) a join -- Match each date to the proper ISO week year @ISO_WEEK b on a.[DATE] between b.[ISO_WEEK_YEAR_START_DATE] and b.[ISO_WEEK_YEAR_END_DATE] order by a.[DATE_ID]
return
Error_Exit:
-- Return a pseudo error message by trying to -- convert an error message string to an int. -- This method is used because the error displays -- the string it was trying to convert, and so the -- calling application sees a formatted error message.
declare @error int
set @error = convert(int,@cr+@cr+ '*******************************************************************'+@cr+ '* Error in function F_TABLE_DATE:'+@cr+'* '+ isnull(@ErrorMessage,'Unknown Error')+@cr+ '*******************************************************************'+@cr+@cr)
return
end
GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
GRANT SELECT ON [dbo].[F_TABLE_DATE] TO [public] GO set dateformat ydm go print 'Checksum with ydm' go select [Checksum] = checksum_agg(binary_checksum(*)) from dbo.F_TABLE_DATE ( '20000101','20101231' ) go set dateformat ymd go print 'Checksum with ymd' go select [Checksum] = checksum_agg(binary_checksum(*)) from dbo.F_TABLE_DATE ( '20000101','20101231' ) go set dateformat ymd go -- Sample select for date range select * from dbo.F_TABLE_DATE ( '20000101','20101231' ) order by 1
CODO ERGO SUM |
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-02-09 : 22:00:24
|
is there a NEXT_WEEKDAY_DATE in there ? [EDIT] I meant NON WEEKEND DATE [/EDIT] ---------------------------------- 'KH' everything that has a beginning has an end |
 |
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-02-09 : 22:18:53
|
quote: Originally posted by khtan
is there a NEXT_WEEKDAY_DATE in there ?
---------------------------------- 'KH' everything that has a beginning has an end
No, that is something I left out intentionally, because I wasn't sure that everyone had the same idea about what a week day was.
If you need to do this a lot, you would be better off with a special built function. If you need it in your calendar table, just add it to your version of F_TABLE_DATE.
If your idea of a weekday is Monday to Friday, this code should give you the next weekday date.
declare @start_date datetime declare @end_date datetime select @start_date = getdate()+1 select @end_date = @start_date+7
select NEXT_WEEKDAY_DATE = min([DATE]) from dbo.F_TABLE_DATE ( @start_date,@end_date) where [DAY_OF_WEEK] between 2 and 6
Edit: Changed code to make it a little faster with fewer dates.
CODO ERGO SUM |
 |
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-02-09 : 22:22:16
|
Yup. Points taken. Thanks for the excellent function
---------------------------------- 'KH' everything that has a beginning has an end |
 |
|
Kristen
Test
22859 Posts |
Posted - 2006-02-10 : 03:56:01
|
"-- Return a pesudo error message by trying to -- convert an error message string to an int. -- This method is used because the error displays -- the string it was trying to convert, and so the -- calling application sees a formatted error message"
I like the sound of that ... I've been looking for a way some code to steal to "return" an error from a Function.
Kristen |
 |
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-02-10 : 05:22:30
|
great function MVJ!
one remark... if either of the input dates exceeds the lower/upper limit this is returned: Server: Msg 242, Level 16, State 3, Line 2 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
so i guess that you don't even have to handle those errors in the function, no?
Go with the flow & have fun! Else fight the flow |
 |
|
Kristen
Test
22859 Posts |
Posted - 2006-02-10 : 06:23:19
|
"so i guess that you don't even have to handle those errors in the function"
If you do trap it then just call my brand spanking new Crash function!!
CREATE FUNCTION dbo.MyCrashFunction ( @strMessage varCHAR(8000) ) RETURNS int BEGIN RETURN convert(int, CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)+ '*******************************************************************'+CHAR(13)+CHAR(10)+ COALESCE(@strMessage,'Unknown Error')+CHAR(13)+CHAR(10)+ '*******************************************************************'+CHAR(13)+CHAR(10))
END GO
-- Test: SELECT dbo.MyCrashFunction('Something terrible happened') GO
which gives:
Server: Msg 245, Level 16, State 1, Procedure MyCrashFunction, Line 7 Syntax error converting the varchar value '
******************************************************************* Something terrible happened ******************************************************************* ' to a column of data type int.
Kristen |
 |
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-02-10 : 09:50:27
|
quote: Originally posted by spirit1
great function MVJ!
one remark... if either of the input dates exceeds the lower/upper limit this is returned: Server: Msg 242, Level 16, State 3, Line 2 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
so i guess that you don't even have to handle those errors in the function, no?
Go with the flow & have fun! Else fight the flow 
Actually, there are valid datetime values that the function cannot process. It rejects dates before 1754-01-01 and after 9997-12-31, because I didn't want to spend a lot of time programming around edge contitions for ISO Weeks. I figured that not being able to load years 1753, 9998, and 9999 would not be a huge drawback for most applications.
CODO ERGO SUM |
 |
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-02-10 : 11:45:48
|
quote: Originally posted by Kristen ..just call my brand spanking new Crash function...
Didn't take you long to steal my idea!
I bet in a few days I'll see a post about Kristen's great techinique for returning error messages from UDFs.
Small minds copy ideas but great minds steal them. 
CODO ERGO SUM |
 |
|
Kristen
Test
22859 Posts |
Posted - 2006-02-10 : 13:16:20
|
"Small minds copy ideas but great minds steal them."
My earlier post (02/10/2006 : 03:56:01) wasn't clear enough. So I've edited it!
Kristen |
 |
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-02-10 : 16:25:02
|
That is very very nice! Thank You MVJ!
We will implement a dw pretty soon, or at least improve on the existing, I sense that the function will save me some coding. Will store the values in a table though.
--*/ (yes the crash function to, Kristen ) --*/
rockmoose |
 |
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-02-10 : 17:10:38
|
quote: Originally posted by rockmoose ...We will implement a dw pretty soon, or at least improve on the existing, I sense that the function will save me some coding. Will store the values in a table though...
A lot of the work I did on this was for a data warehouse that I did years ago. I saw lots of examples that had a few of these ideas, but I wanted a Swiss Army Knife date dimension, the one with 60 blades, so I developed this.
As I said in the comments in the code, this function was meant to load a table. However, if you need it to load a small temporary table, or to use it directly, it works just fine, and is fairly fast for small result sets. You can generate a result set with as little as one row.
Once you load a table, I found it was very useful to create a view that selects the row for the current day. It saves a lot of work in coding to be able to get the data for the current day directly.
CODO ERGO SUM |
 |
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-02-10 : 17:30:02
|
Yes, this was the most comprehensive calender function I've seen yet.
Just got to remove some columns before I let any users see the calendar table. The less options, the less questions [;]
rockmoose |
 |
|
klaus2000
Starting Member
3 Posts |
Posted - 2006-02-22 : 06:31:00
|
receive the following message: (sqlServer2005 - management studio)
Msg 207, Level 16, State 1, Procedure F_TABLE_DATE, Line 502 Invalid column name 'ISO_WEEK_YEAR'. Msg 15151, Level 16, State 1, Line 2 Cannot find the object 'F_TABLE_DATE', because it does not exist or you do not have permission. Msg 208, Level 16, State 1, Line 3 Invalid object name 'dbo.F_TABLE_DATE'.
any ideas?
|
 |
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-02-22 : 11:51:00
|
quote: Originally posted by klaus2000
receive the following message: (sqlServer2005 - management studio)
Msg 207, Level 16, State 1, Procedure F_TABLE_DATE, Line 502 Invalid column name 'ISO_WEEK_YEAR'. Msg 15151, Level 16, State 1, Line 2 Cannot find the object 'F_TABLE_DATE', because it does not exist or you do not have permission. Msg 208, Level 16, State 1, Line 3 Invalid object name 'dbo.F_TABLE_DATE'.
any ideas?
I just copied the script from the posted code, and it ran fine from SQL 2000 Query Analyzer.
Sounds like it did not compile when you ran it, for some reason.
Did you try it in SQL 2000?
CODO ERGO SUM |
 |
|
klaus2000
Starting Member
3 Posts |
Posted - 2006-02-22 : 15:21:57
|
no sql 2k available anymore - migrated everything to ss2005. any plans from your side to have a deeper look at that issue in the near future? btw - thanx for the quick response1
I just copied the script from the posted code, and it ran fine from SQL 2000 Query Analyzer.
Sounds like it did not compile when you ran it, for some reason.
Did you try it in SQL 2000?
CODO ERGO SUM [/quote] |
 |
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-02-22 : 17:36:14
|
It seems that SQL Server 2005 does not like the table prefix on the column alias.
-- Load table with start and end dates for ISO week years ... order by a.[ISO_WEEK_YEAR]
---------------------------------- 'KH' It is inevitable |
 |
|
Kristen
Test
22859 Posts |
Posted - 2006-02-23 : 05:11:02
|
In the ORDER BY?
That's a bit scary isn't it?
Kristen [having not yet migrated anything to SQL2k5] |
 |
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-02-23 : 05:16:13
|
Come to think of it, this is logical as A column alias can be the computation result of columns from different table.
---------------------------------- 'KH' It is inevitable |
 |
|
Kristen
Test
22859 Posts |
Posted - 2006-02-23 : 05:25:54
|
Ah .. I've read it more carefully.
The column [ISO_WEEK_YEAR] is not present in the table alias [A] - so should raise a syntax error in SQL 2k too I reckon!
I bet having stuck my neck out it will get cut off now 
Kristen |
 |
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-02-23 : 07:47:47
|
But SQL Server 2000 let this passed
---------------------------------- 'KH' It is inevitable |
 |
|
Next Page
|
|
|
|
|