🗄️ SQL Server: Fix Two Equal-Looking DATETIME Values Failing to Match
Legacy DATETIME stores time internally in increments of roughly 3.33 milliseconds and rounds any value to the nearest one of those increments. A value computed once and stored, then later recomputed independently elsewhere — from .NET DateTime logic with far finer tick precision — and compared for equality against the original, can fail to match even though both print identically down to the second, because each one’s sub-millisecond rounding landed on a different one of those ~3.33ms increments.
🔎 The Problem
DECLARE @Stored DATETIME = '2026-03-14 09:00:00.003';
DECLARE @Recomputed DATETIME = '2026-03-14 09:00:00.005';
SELECT @Stored AS StoredVal, @Recomputed AS RecomputedVal,
CASE WHEN @Stored = @Recomputed THEN 'MATCH' ELSE 'NO MATCH' END AS Result;
-- Both print as 2026-03-14 09:00:00.003 after rounding, yet compared raw, this can still miss
✅ Fix: Use DATETIME2, or Truncate Before Comparing
- For any new column where sub-second precision matters for equality, use
DATETIME2instead of legacyDATETIME— it stores actual fractional seconds rather than rounding to the nearest ~3.33ms increment. - When stuck with an existing
DATETIMEcolumn, compare values after explicitly truncating both sides to the precision that actually matters, usingDATEADD/DATEDIFFrounding, rather than relying on exact equality from two independent sources. - Avoid recomputing “the same” datetime independently in two different places when you need them to compare equal later — store and reuse the single original value instead of trusting two separate calculations to round identically.
🚨 Why This Is Dangerous
- A row an application confidently reports as “not found” purely because of this rounding mismatch can trigger a duplicate action — a second email, a second charge, a second job run — that an exact-match lookup was specifically supposed to prevent.
DATETIME doesn’t store the moment you gave it — it stores the nearest moment its own rounding allows, and two ‘identical’ moments computed separately don’t always round to the same one.

