Michael Valentine Jones (2/11/2013)

---

ScottPletcher (2/11/2013)

---

Method below works for any and all date and language settings:

DECLARE @startDate datetime

DECLARE @number_of_months int

SET @startDate = GETDATE()

SET @number_of_months = 7

SELECT

DATEADD(DAY, DATEDIFF(DAY, '19000107', last_day_of_month) / 7 * 7, '19000107') AS last_sunday_of_month

FROM (

SELECT DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @startDate) + 1 + month_offset, 0)) AS last_day_of_month

FROM (

SELECT 0 AS month_offset UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL

SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL

SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12

) AS month_offsets

WHERE

month_offset BETWEEN 0 AND (@number_of_months - 1)

) AS derived

ORDER BY

1

Edit: It's not a "magic" number, of course, just a known Sunday. Then the only "assumption" needed for the code to work is every 7 days after that Sunday it will be Sunday again, which is about as safe an assumption as it gets :-).

An issue with your code is that it does not work with dates before 1900-01-06.

That is why I used '17530107' in my code, which is really similar to your code, except that the only issue is with dates before '17530107' for which there is no non-null solution when you use SQL Server datetime.

For example, try the code with this:

SET @startDate = '18470228'