DATEADD Nanoseconds: Round to datetime2 Ticks

DATEADD nanoseconds are rounded to the precision supported by the supplied datetime2 value. I test the rounding boundary instead of assuming every requested increment is stored. A datepart name does not establish single-nanosecond resolution.

Start with an explicitly typed datetime2 value

The query supplies a datetime2(7) value at noon with a zero fractional second. That type supports seven fractional decimal places. Its smallest representable step is one hundred nanoseconds.

Every requested increment is a small nonnegative integer. The DATEADD expression uses the nanosecond datepart. It returns the same datetime2 type supplied by the date argument.

I cast the timestamp before applying DATEADD. A bare date string would invoke a different return-type rule. The explicit source type is part of the precision contract being demonstrated.

The final projection includes both formatted text and the stored fractional nanosecond value. The numeric column makes a zero increment distinguishable without relying only on a client’s fractional-second display.

WITH Increments AS
(
    SELECT CaseId,Nanoseconds FROM (VALUES (1,0),(2,1),(3,49),(4,50),(5,99),(6,100),(7,149),(8,150)) AS v(CaseId,Nanoseconds)
), Calculated AS
(
    SELECT *,DATEADD(nanosecond,Nanoseconds,CAST('2024-02-29T12:00:00.0000000' AS datetime2(7))) AS ResultTime
    FROM Increments
)
SELECT CaseId,Nanoseconds,CONVERT(varchar(27),ResultTime,126) AS ResultText,
    DATEPART(nanosecond,ResultTime) AS StoredFractionNanoseconds
FROM Calculated ORDER BY CaseId;

The first rounding threshold is fifty nanoseconds

Requested increments zero, one and forty-nine expect a stored fractional increment of zero. They do not move this datetime2(7) value to the next representable step. That is the expected rounding behavior.

Requested increments fifty and ninety-nine expect one hundred nanoseconds. A request of exactly one hundred also expects that same stored fraction. Several different requested increments therefore produce one stored result.

The formatted results for those positive stored steps end in .0000001. That final digit represents one ten-millionth of a second. It does not represent one nanosecond.

I keep requests on both sides of the threshold in the model. Testing only an exact hundred-nanosecond increment would conceal the rounding decision. The forty-nine and fifty cases expose the boundary directly.

The next threshold continues the quantization

A request of one hundred forty-nine expects a stored fraction of one hundred nanoseconds. A request of one hundred fifty expects two hundred. The second threshold produces another representable step.

The final formatted value therefore ends in .0000002. The expected numeric fraction is two hundred. Both columns describe the same resulting timestamp at different display units.

The sample starts at an exact fractional boundary so the calculation is easy to review. It does not measure clock accuracy or event timing. These are deterministic arithmetic expectations for supplied literals.

The display conversion can omit the all-zero fraction under the selected ISO style. That formatting detail does not change the stored value. The numeric fraction column remains zero for those first cases.

Do not turn precision into an event-identity promise

Distinct requested times can map to the same representable timestamp. A timestamp alone may therefore be insufficient as an event tie-breaker. An ordering requirement can need a separate unique identifier.

The precision of stored datetime2 is also separate from the accuracy of a source clock. A precise representation does not prove an observation was measured accurately. This example examines arithmetic rather than measurement quality.

Other date and time types have different supported precision and dateparts. This example deliberately uses datetime2(7). It does not generalize the result to datetime or date values.

The query uses made-up increments and changes no settings or tables. It does not test negative rounding or values near the type’s range boundary. Those cases require their own explicit expectations.

When adapting fine-grained time arithmetic, retain values immediately below and at each relevant rounding threshold. Match the input type to the required stored precision. Then keep representation precision separate from the application’s event-order and clock-accuracy requirements.

Try the forty-nine and fifty cases yourself and watch the stored value jump.

A nanosecond argument is not a nanosecond storage guarantee, it is rounded to the value’s supported precision.

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