SQL Server GETDATE(): today, yesterday and the last 7 days

GETDATE() returns the current date and time on the server. Most of the time you want a date range built from it, and the time part is what trips people up.

What GETDATE() returns

SELECT GETDATE() AS now;   -- 2026-10-31 09:14:27.183

It is a datetime: date and time, to the millisecond. Compare a date column against it and the time part comes along for the ride.

Midnight today

Cast it to date to drop the time. That gives you the start of today, which is the anchor for every range below.

SELECT CAST(GETDATE() AS date) AS today;   -- 2026-10-31

Today’s rows

SELECT order_id, total_amount
FROM Orders
WHERE order_date >= CAST(GETDATE() AS date)
  AND order_date <  DATEADD(day, 1, CAST(GETDATE() AS date));

Yesterday

WHERE order_date >= DATEADD(day, -1, CAST(GETDATE() AS date))
  AND order_date <  CAST(GETDATE() AS date)

The last 7 days, not including today

SELECT order_id, order_date, total_amount
FROM Orders
WHERE order_date >= DATEADD(day, -7, CAST(GETDATE() AS date))
  AND order_date <  CAST(GETDATE() AS date)
ORDER BY order_date DESC;

Two ends: greater than or equal to midnight seven days ago, and strictly less than midnight today. This pair works whether the column is a date or a datetime.

Avoid wrapping the column

WHERE CAST(order_date AS date) = CAST(GETDATE() AS date) works, but putting a function on the column can stop SQL Server using an index on it. Comparing the bare column with a range, as above, keeps it fast on big tables.

Related functions

SYSDATETIME() is the same idea with more precision (datetime2). CURRENT_TIMESTAMP is the ANSI spelling of GETDATE(). GETUTCDATE() gives UTC.