SQL查询：如何在任意时间获取过去4个完整周（周一至周日）的数据？

To reliably get the last 4 full weeks (Monday to Sunday) no matter when you run the query, you need to anchor your date range to the start of the 4th previous week and the end of the most recent full week (last Sunday)—instead of just subtracting weeks from the current date directly.

Here are two robust approaches tailored for SQL Server (since you’re using GETDATE()):

Approach 1: Using DATEPART (with explicit week start) #

This method ensures Monday is treated as the first day of the week to avoid inconsistencies from server settings:

-- Force Monday to be the first day of the week (avoids locale-related issues)
SET DATEFIRST 1;

AND datatime BETWEEN 
    -- Start: Monday of 4 weeks ago
    DATEADD(week, -4, DATEADD(day, 1 - DATEPART(weekday, CAST(GETDATE() AS DATE)), CAST(GETDATE() AS DATE)))
    AND 
    -- End: Sunday of last week
    DATEADD(day, -DATEPART(weekday, CAST(GETDATE() AS DATE)), CAST(GETDATE() AS DATE))

• CAST(GETDATE() AS DATE) strips off time values to prevent partial-day mismatches.
• DATEADD(day, 1 - DATEPART(weekday, ...), ...) calculates the Monday of the current week; subtracting 4 weeks shifts this to the Monday of 4 weeks prior.
• DATEADD(day, -DATEPART(weekday, ...), ...) gives the Sunday of last week (since DATEPART(weekday) returns 1 for Monday, subtracting 1 lands on the previous Sunday).

Approach 2: Using DATEDIFF (settings-agnostic) #

This method doesn’t depend on the server’s DATEFIRST setting, as it anchors to the fixed date 1900-01-01 (which was a Monday):

AND datatime BETWEEN 
    -- Start: Monday of 4 weeks ago
    DATEADD(week, DATEDIFF(week, 0, GETDATE()) - 4, 0)
    AND 
    -- End: Sunday of last week
    DATEADD(day, -1, DATEADD(week, DATEDIFF(week, 0, GETDATE()) - 1, 0))

• DATEDIFF(week, 0, GETDATE()) counts the total number of weeks since 1900-01-01.
• DATEADD(week, [count] - 4, 0) jumps back 4 weeks to the Monday of that week.
• DATEADD(week, [count] - 1, 0) gets the Monday of last week; subtracting 1 day gives the Sunday of last week.

Your initial attempt AND datatime between dateadd(week,-4,getdate()) and dateadd(week,-1,getdate()) didn’t work because:

• DATEADD(week, -4, GETDATE()) returns the same day of the week as today, 4 weeks prior (e.g., if today is Wednesday, this gives the Wednesday 4 weeks ago).
• This created a range from [4 weeks ago today] to [1 week ago today], which doesn’t align with full Monday-to-Sunday weeks.

内容的提问来源于stack exchange，提问作者Pabs88