How to pull last week’s orders in SQL Server (not “the last 7 days”)

When someone says “last week’s orders” they mean the calendar week that just finished, not a sliding window of 168 hours. Those two queries return different money.

What they actually asked for

James wants last week. If today is Thursday 30 October, last week is Monday 20 to Sunday 26, not “from last Thursday until now”. A rolling DATEADD(day, -7, GETDATE()) includes this morning and drops half of last Thursday.

UK week: Monday to Sunday

DECLARE @today date = CAST(GETDATE() AS date);
DECLARE @thisMonday date = DATEADD(day, 1 - DATEPART(weekday, DATEADD(day, -1, @today)), @today);
-- weekday of Monday after shifting so Monday = 1, when DATEFIRST is 7 (US default)

SELECT order_id, order_date, total_amount
FROM Orders
WHERE order_date >= DATEADD(week, -1, @thisMonday)
  AND order_date <  @thisMonday
ORDER BY order_date;

SQL Server’s DATEPART(weekday, …) depends on DATEFIRST. The safer habit on a shared server is to pin the week yourself.

A version that does not care about DATEFIRST

DECLARE @today date = CAST(GETDATE() AS date);
DECLARE @thisMonday date = DATEADD(day, -(DATEDIFF(day, '20000103', @today) % 7), @today);
-- 3 Jan 2000 was a Monday

SELECT order_id, customer_id, total_amount, order_date
FROM Orders
WHERE order_date >= DATEADD(day, -7, @thisMonday)
  AND order_date <  @thisMonday;

Send the total with it

SELECT COUNT(*) AS orders, SUM(total_amount) AS revenue
FROM Orders
WHERE order_date >= DATEADD(day, -7, @thisMonday)
  AND order_date <  @thisMonday;

Put the range in the email subject: “Orders Mon 20–Sun 26 Oct”. Then nobody asks whether today is in it.