• You can use the general calc shown below to get the nearest day of any day:

    SELECT

    date,

    DATEADD(DAY, DATEDIFF(DAY, '19000101', date) / 7 * 7, '19000101') AS monday_on_or_before_date,

    DATEADD(DAY, DATEDIFF(DAY, '19000102', date) / 7 * 7, '19000102') AS tuesday_on_or_before_date,

    DATEADD(DAY, DATEDIFF(DAY, '19000103', date) / 7 * 7, '19000103') AS wednesday_on_or_before_date

    FROM (

    SELECT GETDATE()-3 AS date UNION ALL

    SELECT GETDATE()-2 AS date UNION ALL

    SELECT GETDATE()-1 AS date UNION ALL

    SELECT GETDATE() AS date UNION ALL

    SELECT GETDATE()+1 AS date UNION ALL

    SELECT GETDATE()+5 AS date UNION ALL

    SELECT GETDATE()+11 AS date

    ) AS test_dates

    Once that calc gets you the first date you need, you simply add -7, -14, -21, etc., days to go back as many weeks as you want.

    For example, for the last 6 Mondays on or before the current date (whatever that is when the code is run):

    SELECT

    DATEADD(DAY, days_to_add, most_recent_monday) AS last_6_mondays

    FROM (

    SELECT DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '19000101') AS most_recent_monday

    ) AS main_date

    CROSS JOIN (

    SELECT 0 AS days_to_add UNION ALL

    SELECT -7 AS days_to_add UNION ALL

    SELECT -14 UNION ALL

    SELECT -21 UNION ALL

    SELECT -28 UNION ALL

    SELECT -35

    ) AS days_to_add

    ORDER BY 1

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.