CREATE FUNCTION dbo.WeekDates /**************************************************************************************** Purpose: Given some date >= 07 January 0001 abd <- 25 December 9999 and a day value [1=(Monday) thru 7=(Sunday)] to identify the starting day of the week the date is in, return any combination of the following columns: ----------------------------------------------------------------------------------------- Output Columns Available: SoW : Starting date of the week that the given date is in. EoW : End date of the week that the given date is in. (Always SoW+6 days) Accountants love this column for "Week Ending Calculations". It's is NOT good as search or grouping criteria if the related data contains times. Use SnW, instead. SnW : Starting date of the Next week. This is good for search and grouping criteria for the related data using a typical "AND SomeDate >= SoW AND SomeDate < SnW". YY# : Year of the "mid week" date (4th day of the week) as an INT. MM# : Month of the "mid week" date (4th day of the week) as an INT. QQ# : Quarter of the "mid week" date (4th day of the week) as an INT. WK# : Week of the "mid week" date (4th day of the week) as an INT. DW# : Week Day of the given date where day 1 is the SoW as an INT. ----------------------------------------------------------------------------------------- Input Parameters: @SomeDate : DATE : The date or datetime to find the week information for. Can be any expression that will resolve to the DATE data-type. NULL dates, dates prior to 07 January 0001, and dates after 25 December 9999 will not have rows returned for them. @DateFirst : INT ; Can any expression the produces an INTEGER value based on the following chart. All other values will return an empty result set (NO rows returned). Value First Day of Week ----- ----------------- 1 Monday 2 Tuesday 3 Wednesday 4 Thursday 5 Friday 6 Saturday 7 Sunday ----------------------------------------------------------------------------------------- Usage Examples: --===== Basic Syntax SELECT SoW FROM dbo.WeekDates(@SomeDate,@DateFirst) ; --===== Multiple Rows from a source table using @@DateFirst to control the first day of -- the week. SELECT st.Columns ,wd.SoW --Can be any column from the function. ,wd.OtherColumnsFromFunction FROM dbo.SomeTable st CROSS APPLY dbo.WeekDates(st.SomeDateTimeColumn,@@DATEFIRST) wd ; --===== Multiple Rows from a source table with Thursday as the first day of the week. SELECT st.Columns ,wd.SoW --Can be any column from the function. ,wd.OtherColumnsFromFunction FROM dbo.SomeTable st CROSS APPLY dbo.WeekDates(st.SomeDateTimeColumn,4) wd --Thursday = 4 ; --===== Multiple Rows from a source table using a variable to control the first day of -- the week. SELECT st.Columns ,wd.SoW --Can be any column from the function. ,wd.OtherColumnsFromFunction FROM dbo.SomeTable st CROSS APPLY dbo.WeekDates(st.SomeDateTimeColumn,@SomeDayValue1to7) wd ; ----------------------------------------------------------------------------------------- Programmer Notes: 1. Will not return rows for any given date prior to 07 January 0001 nor any date after 25 December 9999. 2. The "day value" must resolve to an INTEGER value between 1 and 7 where 1=Monday thru 7=Sunday. While conversion of DECIMAL and FLOAT values to INTEGER usually round down, there is nothing in this code to guarantee it. In other words, the "day value" uses the same "first of the week" values that DATEFIRST uses. 3. If @@DATEFIRST is used for the "day value", the function will automatically adjust to changes in the DATEFIRST. Otherwise, the "day value" MUST be an expression that resolves to an INT value of 1 thru 7 as previously explained. 4. Performance is best when either @@DATEFIRST or a numeric constant (K) is used for the "day value". Here are the general times for returning just the SoW column for the full range of possible rows, which included 14 ignored rows due to being out of range for a total of 3,652,059 rows. YMMV. The output of the SoW column was shunted to a throw-away variable to remove any result I/O to screen or disk. Adding extra columns to the output (in whatever form) added about 65% more time per column added in tests. |------------------------------------------| | @@DATEFIRST | Numeric K | @Variable | |---+-------------+-------------+--------------| |Run| CPU Elapsed| CPU Elapsed| CPU Elapsed| |---+----- -------+----- -------+------ -------| | 1| 860 860 | 875 861 |1172 1157 | | 2| 859 854 | 843 857 |1125 1132 | | 3| 859 854 | 860 860 |1109 1108 | | 4| 860 857 | 828 851 |1110 1105 | | 5| 859 858 | 844 852 |1125 1113 | | 6| 859 869 | 859 855 |1093 1104 | | 7| 860 856 | 844 847 |1125 1118 | | 8| 875 861 | 844 851 |1110 1103 | | 9| 844 853 | 859 851 |1109 1110 | | 10| 859 855 | 859 852 |1109 1107 | |---+-------------+-------------+--------------| |AVG|859.4 857.7 |851.5 853.7 |1118.7 1115.7| |----------------------------------------------| ----------------------------------------------------------------------------------------- Revision History: Rev 00 - 16 Jun 2022 - Jeff Moden - Initial Proof-of-Principle and related testing. Rev 01 - 23 Jun 2022 - Jeff Moden - Final performance and usage testing/modifications. - Final documentation and ready for release. ****************************************************************************************/ (@SomeDate DATE,@DateFirst INT) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT SoW = v2.SoW --,MoW = v3.MoW --This isn't normally needed in the output except for testing. ,EoW = DATEADD(dd,6,v2.SoW) --Accountants like this column for "week ending" things. ,SnW = DATEADD(dd,7,v2.SoW) --For criteria, this is much safer than EoW (Closed/Open) ,YY# = DATEPART(yy,v3.MoW) ,QQ# = DATEPART(qq,v3.MoW) ,MM# = DATEPART(mm,v3.MoW) ,WK# = (DATEPART(dy,v3.MoW)+6)/7 ,DW# = v1.Offset+1 FROM (VALUES(DATEDIFF(dd,DATEFROMPARTS(1,1,@DateFirst),@SomeDate)%7)) v1(Offset) CROSS APPLY (VALUES(DATEADD(dd,-Offset,@SomeDate))) v2(SoW) CROSS APPLY (VALUES(DATEADD(dd,3,SoW))) v3(MoW) WHERE @SomeDate >= DATEFROMPARTS(1,1,@DateFirst) AND @SomeDate <= DATEFROMPARTS(9999,12,25) AND @DateFirst BETWEEN 1 AND 7 ; GO