15 Nov 2011

Get weeks in a year in SQL

WITH cteCalendar
AS (
    SELECT    DATEADD(DAY, DATEDIFF(DAY, 0, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 7 * Number)) / 7 * 7, 0) AS theMonday,
        1 + Number AS theWeek
    FROM    master..spt_values
    WHERE    Type = 'P'
        AND number BETWEEN 0 AND 52
)
SELECT        theWeek,
        theMonday,
        DATEADD(DAY, 6, theMonday) AS theSunday
FROM        cteCalendar
WHERE        2011 IN (DATEPART(YEAR, theMonday), DATEPART(YEAR, DATEADD(DAY, 6, theMonday)))
ORDER BY    theWeek

No comments:

Post a Comment