Filtering records by today’s date sounds simple enough, but there’s a gotcha that’s easy to miss if you’re not careful. This article starts with the correct approach, then explains why the obvious method fails. So you’ll know what to do and why.
The Right Way to Filter by Today’s Date
The approach you use depends on whether your column is a DATE type or a DATETIME/DATETIME2 type.
If Your Column Is a DATE Type
A DATE column stores no time component, so a simple equality comparison works. The only thing to watch is that GETDATE() returns a datetime value, so you’ll need to cast it to DATE first to match the column type.
SELECT *
FROM orders
WHERE order_date = CAST(GETDATE() AS DATE);If Your Column Is a DATETIME or DATETIME2 Type
A DATETIME or DATETIME2 column stores both date and time, so a direct equality comparison won’t work reliably. The correct approach is to filter by a range covering the full 24-hour window:
SELECT *
FROM orders
WHERE order_date >= CAST(GETDATE() AS DATE)
AND order_date < DATEADD(day, 1, CAST(GETDATE() AS DATE));This captures every row from midnight at the start of today through to the last millisecond before midnight tonight, regardless of what time each record was created.
This range approach also works perfectly fine on DATE columns, so if you want a single consistent query regardless of column type, this is the one to use.
If you’re not sure whether your column is a DATE type or a DATETIME/DATETIME2 type, you can check with this query:
SELECT
COLUMN_NAME,
DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'orders'
AND COLUMN_NAME = 'order_date';The Mistake Most Beginners Make (With DATETIME Columns)
The instinctive approach is to compare the column directly against GETDATE():
SELECT *
FROM orders
WHERE order_date = GETDATE();On a DATE column this actually works. SQL Server handles the implicit conversion and matches on the date portion only. But on a DATETIME or DATETIME2 column, this almost never returns any rows.
The reason is that GETDATE() returns the current date and time down to the millisecond. This could look something like 2026-06-24 09:15:30.123. For a row to match, its order_date would need to be exactly 2026-06-24 09:15:30.123 (the same year, month, day, hour, minute, second, and millisecond). The chance of that happening is almost zero. It would require a row to have been created at the exact millisecond the query runs. Any row created even a millisecond earlier or later won’t match.
But the biggest issue is that there’s no error message. The query runs fine and simply returns zero rows, which makes it a particularly easy mistake to miss.
Why Not Just Convert the Column to DATE?
You might see an alternative approach online that strips the time from the column itself:
SELECT *
FROM orders
WHERE CAST(order_date AS DATE) = CAST(GETDATE() AS DATE);This works, but it’s slower on large tables. Casting GETDATE() as DATE is fine because SQL Server evaluates it once and treats the result as a fixed value. Converting/casting the column itself is the problem. It forces SQL Server to evaluate the function on every single row before it can apply the filter, which prevents it from using an index on order_date efficiently.
The range approach in the recommended solution keeps the column untouched on the left side of the comparison, so any index on order_date can be used as normal. This makes it a sargable query.
Other Useful Variations
Records From Yesterday
SELECT *
FROM orders
WHERE order_date >= DATEADD(day, -1, CAST(GETDATE() AS DATE))
AND order_date < CAST(GETDATE() AS DATE);Records From Tomorrow
SELECT *
FROM bookings
WHERE booking_date >= DATEADD(day, 1, CAST(GETDATE() AS DATE))
AND booking_date < DATEADD(day, 2, CAST(GETDATE() AS DATE));Records From Today Onwards
SELECT *
FROM bookings
WHERE booking_date >= CAST(GETDATE() AS DATE);Records Up to and Including Today
SELECT *
FROM orders
WHERE order_date < DATEADD(day, 1, CAST(GETDATE() AS DATE));Quick Reference
| Goal | Query Pattern |
|---|---|
| Today (DATE column) | WHERE order_date = CAST(GETDATE() AS DATE) |
| Today (DATETIME column) | 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) |
| Tomorrow | WHERE order_date >= DATEADD(day, 1, CAST(GETDATE() AS DATE)) AND order_date < DATEADD(day, 2, CAST(GETDATE() AS DATE)) |
| Today onwards | WHERE order_date >= CAST(GETDATE() AS DATE) |
| Up to and including today | WHERE order_date < DATEADD(day, 1, CAST(GETDATE() AS DATE)) |