Technical Article

Week Starts On Saturday?

,

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
 )

Rate

3 (1)

Share

Share

Rate

3 (1)