Weeknumber based on first sunday of the year

Nagesh CL 621 Reputation points

Hello Team, We have a requirement to calculate week number in dedicated sql pool based on the first Sunday of the year.

For Ex: - First sunday of 2023 is 01-01-2023 and hence

26-12-2022 to 01-01-2023 should be 202301. 02-01-2023 to 08-01-2023 should be 202302 and so on. Similarly: - First sunday of 2025 is 05-01-2025 and hence 30-12-2024 to 05-01-2025 should be 202501. 6-1-2025 to 12-01-2025 should be 202502 and so on. Similarly: - First sunday of 2027 is 03-01-2023 and hence 28-12-2026 to 03-01-2027 should be 202701 04-01-2027 to 10-01-2027 should be 202702 and so on. So, bottomline is First Sunday of the year is the end of the first week of the year (Incremented week on week). Thanks in advance. Regards, Nagesh CL

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,566 questions
0 comments No comments
{count} votes

1 additional answer

Sort by: Most helpful
  1. BhargavaGunnam-MSFT 28,271 Reputation points Microsoft Employee

    Hello Nagesh CL,

    You can use the below code to calculate the first Sunday of the year and then computes the week number based on the difference between the input date and the first Sunday.

    DECLARE @inputDate DATE = '2023-01-01';
    WITH FirstSundayOfYear AS (
        SELECT DATEADD(wk, DATEDIFF(wk, 0, DATEADD(yy, DATEDIFF(yy, 0, @inputDate), 0)) + (DATEPART(dw, DATEADD(yy, DATEDIFF(yy, 0, @inputDate), 0)) % 7), 0) AS FirstSunday
        YEAR(@inputDate) * 100 + (DATEDIFF(wk, FirstSunday, @inputDate) + 1) AS WeekNumber

    I hope this helps.

    Please let me know if you have any further questions.