Calculating Work Week based on Next Sunday Logic in Microsoft SQL Server 2016

Calculating Work Week based on Next Sunday Logic

Introduction

As a technical blogger, I’m often asked to tackle tricky problems related to date calculations. One such problem that caught my attention recently was calculating the work week based on the next Sunday logic. In this article, we’ll explore how to achieve this using Microsoft SQL Server 2016 (SP2-CU11).

Understanding the Problem

The question asks us to calculate the work week starting from the Sunday of the year in which January 1st falls. For example, if January 1st, 2021 is a Friday, we expect WW1 to be the week starting from December 27th, 2020, and ending on January 2nd, 2021.

Background

To solve this problem, we need to understand how to calculate dates in SQL Server. The DATEADD function adds specified interval(s) of time to a date. In contrast, the DATEDIFF function returns the difference between two dates in a specified interval (e.g., days, months).

Here’s an analogy to help you understand:

DATEPART(dy, DATEADD(dd, DATEDIFF(dd,'07 Jan 1753',Cal_Date) /7*7+6,'07 Jan 1753') + 6 calculates the day part of the date that is 7 days after January 1st, 1753.
(DATEPART(dy, DATEADD(dd, DATEDIFF(dd,'07 Jan 1753',Cal_Date) /7*7+6,'07 Jan 1753')) + 6 adds 6 to this result. The +6 represents the day of the week offset from Sunday (0) to Saturday (6).
/7 then divides by 7, which represents the number of days in a week.
Finally, DATEADD(dd, DATEDIFF(dd,'07 Jan 1753',Cal_Date) /7*7+6,'07 Jan 1753') converts this result back to a date. This step effectively rounds down any fractional part to zero.

The SQL Code

Here’s the SQL code that solves the problem:

-- Calculate work week based on next Sunday logic

DECLARE @StartDate DATE = '07 Jan 1753'
DECLARE @StartDateOffset INT = DATEDIFF(dd, '07 Jan 1753', GETDATE()) / 7 * 7 + 6

SELECT 
    -- Get the year in which January 1st falls
    YEAR(DATEADD(dd, -DATEDIFF(dd,'07 Jan 1753',Cal_Date) /7*7+6, '01 Jan 2021')) AS Year,
    -- Calculate work week starting from Sunday of year
    DATEADD(dd, DATEDIFF(dd,'07 Jan 1753',Cal_Date) /7*7+6, '07 Jan 1753') + 6 AS SundayDate,
    DATEADD(dd, DATEDIFF(dd,'07 Jan 1753',Cal_Date) /7*7+6, '07 Jan 1753') + 6 / 7 * 7 AS WeekNumber,
    -- Get the last day of the week
    DATEADD(dd, (DATEPART(dw, GETDATE()) - 1), DATEDIFF(dd,'07 Jan 1753',Cal_Date) /7*7+6) AS LastDayOfWeek

Explanation

Let’s break down the SQL code:

We declare @StartDate as January 1st, 1753. This date is used to calculate the first week of the year.
We then calculate the start offset by dividing the difference between our current date and January 1st, 1753 (in days) by 7. Since we want Sunday (6), we multiply this result by 7 and add 6. This effectively calculates the day of the week in which January 1st falls.
The SELECT statement retrieves four columns:
- Year: The year in which January 1st falls. We calculate this using the YEAR function on a date added to January 1st, 2021 (to force it into that year).
- SundayDate: This is our target Sunday date based on next Sunday logic. We add the start offset to the base Sunday.
- WeekNumber: The week number corresponding to the work week starting from our target Sunday. We divide the result of our offset calculation by 7 and multiply by 7, then add the day part of our Sunday date.
- LastDayOfWeek: This is the last day of the work week. To calculate this, we subtract one from the day of the week (using the modulo operator), which represents Saturday (0).

Example Use Cases

Here are a few example use cases for calculating the work week based on next Sunday logic:

Calculate Work Week: Suppose you want to determine the start date and end date of the next work week.

-- Calculate work week
DECLARE @StartDate DATE = '07 Jan 1753'
DECLARE @StartDateOffset INT = DATEDIFF(dd, '07 Jan 1753', GETDATE()) / 7 * 7 + 6

SELECT 
    DATEADD(dd, -DATEDIFF(dd,'07 Jan 1753',Cal_Date) /7*7+6, '01 Jan 2021') AS StartDate,
    DATEADD(dd, DATEDIFF(dd,'07 Jan 1753',Cal_Date) /7*7+6, '07 Jan 1753') + 6 AS SundayDate
FROM 
    YourTable


*   **Find Next Work Week**: Suppose you want to find the date of the next work week.
    ```markdown
    -- Find next work week
    DECLARE @StartDate DATE = '07 Jan 1753'
    DECLARE @StartDateOffset INT = DATEDIFF(dd, '07 Jan 1753', GETDATE()) / 7 * 7 + 6

    SELECT 
        DATEADD(dd, -DATEDIFF(dd,'07 Jan 1753',Cal_Date) /7*7+6, '01 Jan 2021') AS PreviousStartDate,
        (DATEADD(dd, DATEDIFF(dd,'07 Jan 1753',Cal_Date) /7*7+6, '07 Jan 1753') + 6) AS NextSundayDate
    FROM 
        YourTable

Conclusion

Calculating the work week based on next Sunday logic is a useful technique in various applications. In this article, we explored how to achieve this using Microsoft SQL Server 2016 (SP2-CU11). We covered the necessary steps, including understanding date calculations and writing effective SQL code.

We also provided example use cases for calculating the start date and end date of the next work week, as well as finding the date of the next work week. With these techniques in your toolkit, you’ll be better equipped to tackle similar challenges in your own projects.

Last modified on 2025-04-11