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
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