Comparing dates in SQL Server works the same way as comparing numbers. The same operators apply, the logic is the same, and the results behave exactly as you’d expect (but with a couple of gotchas worth knowing about).
The Basic Comparison Operators
SQL Server supports the following operators for comparing date values:
| Operator | Meaning | Example |
|---|---|---|
| = | Equal to | order_date = ‘2026-06-24’ |
| <> | Not equal to | order_date <> ‘2026-06-24’ |
| > | After | order_date > ‘2026-06-24’ |
| >= | On or after | order_date >= ‘2026-06-24’ |
| < | Before | order_date < ‘2026-06-24’ |
| <= | On or before | order_date <= ‘2026-06-24’ |
| BETWEEN | Within a range (inclusive) | order_date BETWEEN ‘2026-01-01’ AND ‘2026-06-24’ |
These all work in WHERE clauses, JOIN conditions, and CASE expressions.
Comparing Dates in a WHERE Clause
The most common use case is filtering rows by date in a WHERE clause. Here are the patterns you’ll use most often.
Exact Match
SELECT *
FROM orders
WHERE order_date = '2026-06-24';After a Date
SELECT *
FROM orders
WHERE order_date > '2026-06-01';Before a Date
SELECT *
FROM orders
WHERE order_date < '2026-06-01';Within a Date Range
SELECT *
FROM orders
WHERE order_date BETWEEN '2026-01-01' AND '2026-06-30';BETWEEN is inclusive on both ends, meaning it includes rows where order_date equals either boundary date.
Comparing Against Today’s Date
GETDATE() returns the current date and time, which makes it useful for comparisons that need to be relative to today rather than a fixed date.
Records From Today
SELECT *
FROM orders
WHERE order_date >= CAST(GETDATE() AS DATE)
AND order_date < DATEADD(day, 1, CAST(GETDATE() AS DATE));The CAST(GETDATE() AS DATE) strips the time component so the comparison works against the date only. More on why that matters in the next section.
Records From the Last 30 Days
SELECT *
FROM orders
WHERE order_date >= DATEADD(day, -30, GETDATE());Records From the Last 12 Months
SELECT *
FROM orders
WHERE order_date >= DATEADD(month, -12, GETDATE());Records From This Year
SELECT *
FROM orders
WHERE YEAR(order_date) = YEAR(GETDATE());Future Records
SELECT *
FROM bookings
WHERE booking_date > GETDATE();The BETWEEN Gotcha
BETWEEN is inclusive, which is usually what you want. But if your column stores a datetime rather than just a date, the upper boundary can cause problems.
Take this query:
SELECT *
FROM orders
WHERE order_date BETWEEN '2026-06-01' AND '2026-06-30';If order_date is a DATETIME column, any row with a timestamp of 2026-06-30 09:15:30 will be excluded. SQL Server interprets '2026-06-30' as 2026-06-30 00:00:00, so anything after midnight on June 30 falls outside the range.
The safest way to handle this is to use >= and < instead:
SELECT *
FROM orders
WHERE order_date >= '2026-06-01'
AND order_date < '2026-07-01';This captures everything from the start of June 1 through to the last millisecond of June 30, regardless of the time component. It’s a good habit to use this pattern any time you’re working with datetime columns.
Using DATEDIFF() in Comparisons
DATEDIFF() calculates the difference between two dates and returns an integer. That integer can be used directly in a comparison, which makes it useful for filtering by age or elapsed time.
Records Older Than 90 Days
SELECT *
FROM orders
WHERE DATEDIFF(day, order_date, GETDATE()) > 90;Records Created Within the Last Week
SELECT *
FROM orders
WHERE DATEDIFF(day, order_date, GETDATE()) <= 7;Comparing Two Date Columns
DATEDIFF() works on two columns as well, not just a column and a fixed date. This is useful when you want to compare related dates within the same row. For example, finding orders that took more than 5 days to ship.
SELECT *
FROM orders
WHERE DATEDIFF(day, order_date, shipped_date) > 5;One thing to keep in mind: DATEDIFF() counts boundary crossings rather than elapsed time. DATEDIFF(day, '2026-06-30', '2026-07-01') returns 1, even though only a few seconds might separate those two timestamps. For most filtering purposes that’s fine, but it’s worth knowing if precision matters.
A Note on Date Formats in Comparisons
When writing date literals in comparisons, always use the YYYY-MM-DD format. It’s unambiguous and SQL Server interprets it consistently regardless of regional settings.
Formats like '06/24/2026' or '24-06-2026' can be misread depending on the server’s language settings, which leads to errors or silently wrong results. '2026-06-24' is always safe.