Why datetime Turns .999 Milliseconds Into the Next Day

The last-looking timestamp of a day can become the first timestamp of the next day. With datetime, 999 milliseconds at 23:59:59 rounds upward, so an inclusive report boundary can admit tomorrow's rows.

Inspect the Stored Value Before the Predicate

The familiar datetime type does not store every possible millisecond value exactly. Its fractional seconds follow increments based on one three-hundredth of a second, displayed in the familiar .000, .003, and .007 pattern. The input text and the stored value can therefore differ before a query evaluates its WHERE clause.

I inspect the typed value before blaming the report predicate. A conversion at midnight can make the predicate operate exactly as written against a boundary the author did not intend. Display the value with a format that includes the date and fractional time, rather than relying on a grid cell that hides the changed day.

DECLARE @Legacy datetime=CONVERT(datetime,'2026-09-20T23:59:59.999',126);
SELECT CONVERT(varchar(30),@Legacy,121) AS StoredLegacyValue,
       CONVERT(date,@Legacy) AS StoredDate;

The rounding rule explains the next-day boundary in this example. It is a documented type behavior, not a timing result measured from a particular workload. Review the input parameter type too: the same conversion can happen in application code or a stored-procedure parameter before the table comparison begins.

Show Where 999 Milliseconds Lands on the Rounding Grid

Compare several nearby inputs to see that the storage rule is a rounding grid rather than simple truncation to three decimal places. The selected values make the end-of-day boundary visible. Do not infer that every printed three-digit fraction represents an equally spaced millisecond value inside datetime.

SELECT v.InputText,
       CONVERT(varchar(30),CONVERT(datetime,v.InputText,126),121) AS StoredValue
FROM (VALUES
('2026-09-20T23:59:59.993'),
('2026-09-20T23:59:59.997'),
('2026-09-20T23:59:59.998'),
('2026-09-20T23:59:59.999')) AS v(InputText);

Using 999 milliseconds as a supposed universal last instant of the day relies on precision that the legacy type does not support. Replacing it with another hand-picked fraction only ties the query to a particular storage precision. A report boundary should survive a future move to a more precise timestamp type.

Compare datetime2 Without Recreating Lost Precision

The datetime2 type, introduced in SQL Server 2008, supports an explicit fractional precision up to seven digits. A datetime2 value at the chosen millisecond precision can retain the input fraction that the legacy type rounds. Choose the precision from the accepted data contract, not merely the largest available number.

DECLARE @Original datetime2(3)='2026-09-20T23:59:59.999';
DECLARE @Legacy datetime=CONVERT(datetime,@Original);
SELECT @Original AS OriginalValue,@Legacy AS LegacyValue,
       CONVERT(datetime2(3),@Legacy) AS ConvertedBackValue;

Converting the rounded legacy value back to a newer type cannot reconstruct the lost original fraction. It preserves the rounded value at the new representation's precision. Existing rows therefore need a provenance decision when changing column types; an ALTER COLUMN is not a time machine for precision that was never stored.

Higher representational precision also does not prove that the source clock was accurate to that precision. Keep event-clock accuracy, timestamp storage, and reporting boundaries as separate concerns. The database can store a very precise representation of an imprecisely observed event.

Reproduce the 999 Milliseconds Boundary Error

The following temporary table uses datetime2 with enough precision to expose several boundary cases. It contains an ordinary time, two late-day values, and exact next-day midnight. Those are synthetic inputs chosen to test the predicate's contract, rather than records from an actual incident.

CREATE TABLE #DayEvents
(
    EventID int NOT NULL PRIMARY KEY,
    EventAt datetime2(7) NOT NULL
);
INSERT #DayEvents VALUES
(1,'2026-09-20T12:00:00'),
(2,'2026-09-20T23:59:59.9990000'),
(3,'2026-09-20T23:59:59.9999999'),
(4,'2026-09-21T00:00:00');
DECLARE @DayStart date=DATEFROMPARTS(2026,9,20);
DECLARE @BadEnd datetime='2026-09-20T23:59:59.999';
SELECT EventID,EventAt FROM #DayEvents
WHERE EventAt BETWEEN @DayStart AND @BadEnd
ORDER BY EventAt;

BETWEEN includes both endpoints. The rounded upper parameter is midnight tomorrow, so that exact next-day value qualifies under the predicate. The timestamp column's greater precision does not repair the already rounded parameter. Midnight has a remarkable talent for joining yesterday's report when invited by an inclusive boundary.

Why 999 Milliseconds Still Fails With datetime2

An exact datetime2 endpoint ending at .999 is still too early for values with a larger fractional precision. It can exclude valid events later in the final millisecond. Replacing the legacy parameter type therefore fixes the rounding example without making the hand-written last-instant pattern generally correct.

DECLARE @DayStart date=DATEFROMPARTS(2026,9,20);
DECLARE @StillFragileEnd datetime2(3)='2026-09-20T23:59:59.999';
SELECT EventID,EventAt FROM #DayEvents
WHERE EventAt BETWEEN @DayStart AND @StillFragileEnd
ORDER BY EventAt;

The report should describe a day independently of the column's fractional scale. Avoid manufacturing an endpoint with a string of nines whose length must track every schema and parameter change. The next day's start is a clearer boundary and requires no guess about the last representable instant.

Use a Start-Inclusive and End-Exclusive Range

Keep values at or after the day's start and strictly before the next day's start. This includes every supported fractional value inside the day while excluding exact next-day midnight. The same relationship works with legacy and newer timestamp columns when the parameters represent the intended boundaries.

DECLARE @DayStart date=DATEFROMPARTS(2026,9,20);
DECLARE @NextDay date=DATEADD(DAY,1,@DayStart);
SELECT EventID,EventAt FROM #DayEvents
WHERE EventAt>=@DayStart AND EventAt<@NextDay
ORDER BY EventAt;

I use this interval shape before adding display formatting to daily reports. It leaves the stored timestamp column unwrapped in the predicate, allowing an appropriate timestamp-leading index to be considered. Inspect the actual plan for the real workload rather than promising an index seek from syntax alone.

Validate the requested date domain before calculating the next day. The maximum supported calendar date has no representable following date in the same type. A general report API should reject or explicitly handle that boundary instead of letting DATEADD fail unexpectedly.

Keep Time Zones and Application Types Explicit

A day is relative to an accepted calendar and time zone. If events are stored in UTC but the report means a local day, derive the local start and next-day start. Then convert both boundaries to the stored time convention. Daylight-saving transitions can make that interval differ from twenty-four elapsed hours.

Send typed parameters through the application's supported data-access path. Avoid locale-dependent date strings and an accidental datetime parameter when the contract needs datetime2. Inspect both the application binding and stored-procedure declaration during diagnosis. A correctly typed table cannot protect itself from every earlier conversion in the request path.

Test the Boundary Contract Before Release

Which event should belong to each adjacent day? Include exact starts, late fractions, midnight, month transitions, leap days, and the application's accepted zone transitions in the test cases. Compare expected membership rather than only comparing total row counts, since opposite boundary errors can conceal each other numerically.

The 999 milliseconds example is useful because it exposes a broader habit: guessing a last instant instead of defining an interval. Keep the exclusive next boundary and the accepted timestamp types visible, and the report stays understandable as precision changes.

Related reading on this blog: Puzzle: Datatime to DateTime2 Conversation in SQL Server 2017 and Preventing Overlapping Date Ranges in a Table.

A last-looking timestamp is not a reliable day boundary, it is a value subject to the data type's precision and rounding rules.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.