t-clausen.dk's answer works, but it may not be clear why it works (neither to you, nor to the developer who comes for you). Since the added clarity sometimes comes at the expense of brevity and performance, I would like to explain why it works if you prefer to use this 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 Internal
SQL Server stores datetime as two 4-byte integers; the first four bytes represent the number of days from 1/1900 (or up to 1/1/1900, for negative numbers), and the second four milliseconds since midnight.
Using datetime with int or decimal
Since datetime is stored as two 4-byte integers, it is easy to navigate between numeric and dated 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) same as 2011-08-24 10:37:38.783 .
1/1/1900
Since the first integer part of datetime is, as mentioned above, the number of days since January 1, 1900 (also called the SQL Server era), CAST(0 as datetime) by definition equivalent to 1900-01-01 00:00:00
DATEDIFF (day, 0, GETDATE ())
Here, where everything begins to become both cunning and funny. First, we have already established that when 0 treated as a date, it is 1/1/1900, so DATEDIFF(day, '1/1/1900', GETDATE()) same as DATEDIFF(day, 0, GETDATE()) - and will return the current number of days from 1/1/1900. But wait: the current number of days is what is represented by four bytes of date and time! In one statement, we did two things: 1) we removed the part of the time returned by GETDATE() , and we have an integer value that we can use to calculate the last Sunday and the previous Monday a little easier.
Modulo 7 for Monday
DATEDIFF(day, 0, GETDATE()) % 7 uses the fact that DATEPART(day, 0) (which, when re-evaluating the point, matches DATEPART(day, '1/1/1900') ), returns 2 (Monday) . 1 Therefore, DATEDIFF(day, 0, GETDATE()) % 7 will always give the number of days from Monday for the current date.
1 If you have not changed the default behavior using DATEFIRST .
Last Monday
It's almost too trivial for his own title, but for now, we can put together everything we have:
GETDATE() - DATEDIFF(day, 0, GETDATE()) %7 gives you the last MondayDATEDIFF(day, 0, GETDATE() - DATEDIFF(day, 0, GETDATE()) %7) gives you the last Monday as an integer date without any part of the time.
But the poster wanted everything from Monday to Sunday ...
Instead of using the BETWEEN operator, which is included, the published answer excluded the last day, so there is no need to make any changes or calculations to get the correct date range:
t.yourdate >= a.lastmonday - 7 returns records with dates from (or including) midnight on the second last Monday.t.yourdate < a.lastmonday returns records with dates until (but not including) midnight on the last 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 in a year (which may be for you). I believe that the answer I posted earlier should be clear even to novice T-SQL programmers. However, t-clausen.dk's answer is concise, works well and can be found in production code, so I would like to give some explanation to help future visitors understand why it works.