SQLTeam.com | Weblogs | Forums

Number weeks from April to September for every year

I want to number weeks from April to September for every year, such that week 1 starts 1st of April and ends the following Saturday and week 2 starts first Sunday of April and ends the following Saturday and so on.... till the end of September. How do I do that using TSQL.

Thanks in advance.

;WITH cte_april_01 AS (
    SELECT DATEADD(MONTH, 3, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)) AS april_01
),
cte_tally10 AS (
    SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cte_tally100 AS (
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS week# FROM cte_tally10 c1 CROSS JOIN cte_tally10 c2
)
SELECT t.week#, DATEADD(DAY, -DATEDIFF(DAY, 6, april_01) % 7 + (7 * (t.week# - 1)), CAST(april_01 AS date)) AS week_start
FROM cte_april_01 a
INNER JOIN cte_tally100 t ON DATEADD(DAY, -DATEDIFF(DAY, 6, april_01) % 7 + (7 * (t.week# - 1)), CAST(april_01 AS date)) <
    DATEADD(MONTH, 6, april_01)
ORDER BY t.week#
2 Likes

Btw, this code works with any @@DATEFIRST setting. That is, the code is completely any independent of any date and/or language settings and should work correctly under any/all settings.