Weeknumber based on first sunday of the year

Question

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

Answer 1

I would recommend that you create a calendar table where you compute this mapping once for all, so that you don't need to repeat complicated expressions again and again.

Answer 2

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
)
SELECT
    YEAR(@inputDate) * 100 + (DATEDIFF(wk, FirstSunday, @inputDate) + 1) AS WeekNumber
FROM
    FirstSundayOfYear;

I hope this helps.

Please let me know if you have any further questions.