Week Starts On Saturday?

salmon_p, 2012-02-29 (first published: 2012-02-15)

This function: fn_Week_Period_Act() was designed based on accounting (payroll) periods that start on a Saturday. This can be applied against a set of records like timesheets or checks. Then this information can be aggregated based on the accounting week (sat - fri) instead of the calendar week (sun - sat). The function is an inline table function with 1 (datetime) parameter. This can be called by itself as:

SELECT
  CurrentDate,
  WeekID,
  StartWeek,
  EndWeek
FROM dbo.fn_Week_Period_Act(CURRENT_TIMESTAMP)

This can also be JOINed or CROSS APPLYed to an existing table as:

SELECT
  ts.Emp_ID,
  ts.Name,
  ts.Work_Date,
  wpa.WeekID,
  wpa.StartWeek,
  wpa.EndWeek
FROM dbo.TimeSheets AS ts
CROSS APPLY dbo.fn_Week_Period_Act(ut.Work_Date) AS wpa

Have a good day and enjoy.

Terry Steadman

IF (EXISTS(SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[fn_Week_Period_Act]') AND ([type]='IF' OR [type]='FN' OR [type]='TF'))) DROP FUNCTION [dbo].[fn_Week_Period_Act] GO SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[fn_Week_Period_Act](@inDate DateTime) RETURNS TABLE AS /* fn_Week_Period_Act() :: Return week period based on accounting week starting on Saturday. - SET DATEFIRST 6 -- Saturday Is First Day Of Week - Can't use in a function. This function will work when DATEFIRST (@@DATEFIRST) = 7 (week start on Sunday). This is the standard for most english language databases. */ RETURN ( /* Calculate the start date and end date based on StartDateDiff. */ SELECT s2.CurrentDate, s2.WeekID, DATEADD(dd, s2.StartDateDiff, s2.CurrentDate) AS StartWeek, DATEADD(dd, 6 + s2.StartDateDiff, s2.CurrentDate) AS EndWeek FROM ( /* Find the distance to the start date of the week based on current day of week as StartDateDiff. - ex. monday(3) = (-7 + (7 - ((3) - 1))) = distance: -2 */ SELECT s1.CurrentDate, s1.WeekID, (-7 + (7 - (s1.MyDay - 1))) AS StartDateDiff FROM ( /* Return Passed In Date, Add 1 to the date to calc the weekday like DATEFIRST = 6 (Saturday is first day of week) - ex. sat: 1, fri: 7 Add 1 to the week in year if the date is a sunday (7th day in the week). - This must be based on the unmodified date passed in to prevent week/year reset at end of year. */ SELECT @inDate AS CurrentDate, DATEPART(weekday, DATEADD(dd, 1, @inDate)) AS MyDay, DATEPART(week, @inDate) + CAST((DATEPART(weekday, @inDate) / 7) AS int) AS WeekID ) AS s1 ) AS s2 )