Always return data from last week's Monday thru Sunday

Question

How to write a sql statement that always returns data from last Monday to the last Sunday? Any guidance is appreciated.

Thanks.

Answer 1

t-clausen.dk's answer does work, but it may not be clear why it works (neither to you nor to the developer who comes after you). Since added clarity sometimes comes at the cost of conciseness and performance, I'd like to explain why it works in case you'd prefer to use that shorter syntax.

SELECT t.*  
FROM <table> t CROSS JOIN 
(SELECT DATEDIFF(day, 0, getdate() - DATEDIFF(day, 0, getdate()) %7) lastmonday) a 
WHERE t.yourdate >= a.lastmonday - 7 and yourdate < a.lastmonday 

How SQL Server Stores datetime Internally

SQL Server stores datetime as two 4-byte integers; the first four bytes represents the number of days since 1/1/1900 (or before 1/1/1900, for negative numbers) and the second four bytes represents the number of milliseconds since midnight.

Using datetime with int or decimal

Because datetime is stored as two 4-byte integers, it is easy to move between numeric and date data types in T-SQL. For example, SELECT GETDATE() + 1 returns the same as SELECT DATEADD(day, 1, GETDATE()), and CAST(40777.44281 AS datetime) is the same as 2011-08-24 10:37:38.783.

1/1/1900

Since the first integer portion of datetime is, as was mentioned above, the number of days since 1/1/1900 (also called the SQL Server Epoch), CAST(0 as datetime) is by definition equivalent to 1900-01-01 00:00:00

DATEDIFF(day, 0, GETDATE())

Here's where things start to get both tricky and fun. First, we've already established that when 0 is treated as a date, it's the same as 1/1/1900, so DATEDIFF(day, '1/1/1900', GETDATE()) is the same as DATEDIFF(day, 0, GETDATE())—both will return the current number of days since 1/1/1900. But, wait: the current number of days is exactly what is represented by the first four bytes of datetime! In a single statement we have done two things: 1) we've removed the "time" portion returned by GETDATE() and we've got an integer value we can use to make the calculation for finding the most recent Sunday and the previous Monday a little easier.

Modulo 7 for Monday

DATEDIFF(day, 0, GETDATE()) % 7 takes advantage of the fact that DATEPART(day, 0) (which, at the risk of overemphasizing the point, is the same as DATEPART(day, '1/1/1900')) returns 2 (Monday).¹ Therefore, DATEDIFF(day, 0, GETDATE()) % 7 will always yield the number of days from Monday for the current date.

¹Unless you have changed the default behavior by using DATEFIRST.

Most Recent Monday

It's almost too trivial for its own heading, but at this point we can put together everything we've got so far:

• GETDATE() - DATEDIFF(day, 0, GETDATE()) %7 gives you the most recent Monday
• DATEDIFF(day, 0, GETDATE() - DATEDIFF(day, 0, GETDATE()) %7) gives you the most recent Monday as a whole date, with no time portion.

But the poster wanted everything from Monday to Sunday...

Instead of using the BETWEEN operator, which is inclusive, the posted answer excluded the last day, so that there was no need to do any shifting or calculating to get the right date range:

• t.yourdate >= a.lastmonday - 7 returns records with dates since (or including) midnight on the second-most-recent Monday
• t.yourdate < a.lastmonday returns records with dates before (but not including) midnight on the most recent Monday.

I am a big proponent of writing code that is easy to understand, both for you and for the developer who comes after you a year later (which might also be you). I believe that the answer I posted earlier should be understandable even to novice T-SQL programmers. However, t-clausen.dk's answer is concise, performs well, and could be encountered in production code, so I wanted to give some explanation to help future visitors understand why it works.

Answer 2

I realized my code was too complex, so I changed my answer to this. The minus one after getdate() corrects this to return the last Monday on Sunday instead of this weeks Monday (tomorrow if today is Sunday).

SELECT t.* 
FROM <table> t CROSS JOIN
(SELECT DATEDIFF(week, 0, getdate() - 1) * 7 lastmonday) a
WHERE t.yourdate >= a.lastmonday - 7 and yourdate < a.lastmonday

Old code

SELECT t.* 
FROM <table> t CROSS JOIN
(SELECT DATEDIFF(day, 0, getdate() - DATEDIFF(day, 0, getdate()) %7) lastmonday) a
WHERE t.yourdate >= a.lastmonday - 7 and yourdate < a.lastmonday