Always return data from last week's Monday thru Sunday

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

Thanks.

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.

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

You did not specify which SQL dialect, so I will answer for T-SQL, which is what I know best, and you've used the tsql tag.

In T-SQL, use the DATEPART function to find the day of the week. When you know the current day of the week, you can get the date of the most recent Sunday and the Monday before it.

In a stored procedure, it's easier—at least, more readable and easier to maintain, in my opinion—to calculate the values for the most recent Sunday and the preceding Monday and store the values in variables. Then those variables can be used in calculations later in the procedure.

CREATE PROCEDURE SomeProcedure
AS
  DECLARE @CurrentDayOfWeek int, @LastSunday date, @LastMonday date
  SET @CurrentWeekday = DATEPART(weekday, GETDATE())
  -- Count backwards from today to get to the most recent Sunday.
  -- (@CurrentWeekday % 7) - 1 will give the number of days since Sunday; 
  -- -1 negates for subtraction.
  SET @LastSunday = DATEADD(day,  -1 * (( @CurrentWeekday % 7) - 1), GETDATE())
  -- Preceding Monday is obviously six days before last Sunday.
  SET @LastMonday = DATEADD(day, -6, @LastSunday)

  SELECT ReportColumn1, ReportColumn2
  FROM ReportTable
  WHERE DateColumn BETWEEN @LastMonday AND @LastSunday

If you need to be able to do the calculation in a SELECT statement or a view, it's trivial now that we've worked out the steps, though the query itself is a little messier:

  SELECT ReportColumn1, ReportColumn2
  FROM ReportTable
  WHERE DateColumn 
  BETWEEN 
  (
      -- Last Monday is six days before...
      DATEADD(day, -6, 
         -- ... last Sunday.
         DATEADD(day,  -1 * (( DATEPART(weekday, GETDATE()) % 7) - 1), GETDATE())
      )
  )
  AND
  (
      -- Last Sunday has to be calculated again each time it is used inline.
      DATEADD(day,  -1 * (( DATEPART(weekday, GETDATE()) % 7) - 1), GETDATE())
  )

The parentheses I added are not necessary, but are only there to help you see how the WHERE clause is built.

Finally, note that these use the SQL 2008 date data type; for the datetime data type, you may need to perform your own conversion/truncation to compare whole dates instead of date-plus-time values.

The short code in @t-clausen.dk answer doesn't work for Sunday nor does the first result I found on Google. To test them out, try.

DECLARE @date as DATETIME;
SET @date = '2014-11-23 10:00:00'; -- Sunday at 10a

SELECT DATEADD(wk, DATEDIFF(wk,0,@date), 0) -- 2014-11-24 00:00:00
SELECT CAST(DATEDIFF(week, 0, @date)*7 as datetime) -- 2014-11-24 00:00:00
SELECT CAST(DATEDIFF(day, 0, CAST(@date as DATETIME) - DATEDIFF(day, 0,@date) %7) as DATETIME) --2014-11-17 00:00:00
SELECT DATEADD(wk, DATEDIFF(wk,0,@date-1), 0) --2014-11-17 00:00:00

Thus, you should use the long code from the original answer or modified short code.

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