Blog article
SQL Server date ranges: include the whole day
BETWEEN can miss events later on the end date when the column stores a time. A half-open range gives an exact boundary and works with datetime2 precision.
On this page8 sections
You need every order recorded on 5 March. The column is datetime2, so each value includes a time. BETWEEN with an upper bound at midnight on 5 March includes the start of that day, but misses the rest of it. The examples below create a small datetime2 data set in a CTE; the site’s practice dataset stores dbo.Orders.OrderDate as date and cannot show this missed-hours case.
A boundary that looks right
BETWEEN stops at midnight
Both bounds are midnight at the start of 5 March. BETWEEN is inclusive at both ends, so only the first row qualifies.
WITH Orders(OrderID, OrderDate) AS (
SELECT v.OrderID, CONVERT(datetime2(0), v.OrderDate)
FROM (VALUES
(1, '2026-03-05T00:00:00'),
(2, '2026-03-05T14:30:00'),
(3, '2026-03-06T00:00:00')
) AS v(OrderID, OrderDate)
)
SELECT OrderID
FROM Orders
WHERE OrderDate BETWEEN CONVERT(datetime2(0), '2026-03-05T00:00:00', 126) AND CONVERT(datetime2(0), '2026-03-05T00:00:00', 126)
ORDER BY OrderID;| OrderID |
|---|
| 1 |
Use an exclusive next-day bound
The half-open range includes the full day
The lower boundary is included and the next midnight is excluded. The result includes 00:00 and 14:30, but not the next day.
WITH Orders(OrderID, OrderDate) AS (
SELECT v.OrderID, CONVERT(datetime2(0), v.OrderDate)
FROM (VALUES
(1, '2026-03-05T00:00:00'),
(2, '2026-03-05T14:30:00'),
(3, '2026-03-06T00:00:00')
) AS v(OrderID, OrderDate)
)
SELECT OrderID
FROM Orders
WHERE OrderDate >= CONVERT(datetime2(0), '2026-03-05T00:00:00', 126)
AND OrderDate < CONVERT(datetime2(0), '2026-03-06T00:00:00', 126)
ORDER BY OrderID;| OrderID |
|---|
| 1 |
| 2 |
Do not invent an end-of-day time
A bound such as 23:59:59 can miss fractional seconds. A bound such as 23:59:59.999 may be rounded or interpreted differently across date/time types. The next-day exclusive boundary avoids depending on the column precision.
Adjacent ranges should not overlap
Half-open ranges compose cleanly. A March 5 report uses [March 5, March 6); a March 6 report uses [March 6, March 7). An event exactly at March 6 midnight belongs to the second report and cannot be counted twice. By contrast, two inclusive BETWEEN ranges can both include a shared boundary if their endpoints touch.
Month boundaries use the same rule
For a monthly report, use the first instant of the month as the inclusive start and the first instant of the next month as the exclusive end. Do not assume each month has 30 days. In application code, calculate calendar boundaries with a date library and pass them as typed parameters. For a one-day T-SQL query, DATEADD(day, 1, @Day) can calculate the next date after a validated date value within the supported date range.
Check column type and time convention
If OrderDate is a date column with no time, a simple equality against a typed date parameter may already express a single day clearly. The half-open pattern is especially useful for datetime or datetime2 values that include time. If timestamps are stored as UTC instants but the request is for a local calendar day, convert the local start and next local midnight to UTC separately before applying the range; do not assume the offset stays constant through a daylight-saving transition.
Use parameters in application code
For a requested start date and exclusive end date, pass both as typed parameters rather than concatenating user input into SQL. Keep the predicate on the stored column. An index on a timestamp column may help, but check the actual plan and data distribution before claiming a speed gain.
Test the three boundaries
For a new report, include one record exactly at the start, one just before the exclusive end, and one exactly at the exclusive end. The first two should appear; the third should not. Keep the test values in the same data type as the real column so precision and conversion behaviour match the deployed query.