Guides › Yesterday’s sales

Yesterday’s sales in SQL Server: a date filter that does not leak today

“Yesterday” sounds like one line. Most leaked reports use GETDATE() on both ends and quietly include this morning.

The range

DECLARE @today date = CAST(GETDATE() AS date);

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

Lower bound is midnight yesterday. Upper bound is midnight today, not included. Works for date and datetime columns.

These two look right and are not

WHERE CAST(order_date AS date) = CAST(GETDATE() AS date) is today. WHERE order_date >= DATEADD(day, -1, GETDATE()) starts at this time yesterday and has no end, so today’s rows come with it.

One number for the stand-up

SELECT COUNT(*) AS orders,
       SUM(total_amount) AS revenue,
       SUM(CASE WHEN status = 'Cancelled' THEN 1 ELSE 0 END) AS cancelled
FROM Orders
WHERE order_date >= DATEADD(day, -1, CAST(GETDATE() AS date))
  AND order_date <  CAST(GETDATE() AS date);

If they also want “same day last week”, subtract 8 and 7 days from midnight today. Same shape, different bounds.

Try it on Harbour & Co

James Rowe (Sales Manager) needs this now: “My “yesterday’s orders” query keeps showing this morning’s orders too. Fix it?” Write the query, run it, send the results. On the paid plan.

Take the ticket →

Learn it on a real ticket

Monday is free on WorkSQL. A colleague’s ticket lands, you write the T-SQL, we mark it. No videos. Five minutes.

Clock in free See Monday’s tickets