Skip to content
  • Home
  • About
  • No Access
  • Privacy Policy
Bits of .NET
Bits of .NET
  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy

Bits of .NET

Daily micro-tips for C#, SQL, performance, and scalable backend engineering.

  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy
SQL

SQL Server: Fix Two Equal-Looking DATETIME Values Failing to Match

- 15.11.26 - ErcanOPAK

🗄️ 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 DATETIME2 instead of legacy DATETIME — it stores actual fractional seconds rather than rounding to the nearest ~3.33ms increment.
  • When stuck with an existing DATETIME column, compare values after explicitly truncating both sides to the precision that actually matters, using DATEADD/DATEDIFF rounding, 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.

— Database Engineer

Related posts:

SQL: Read Execution Plans to Find Performance Bottlenecks

SQL: Read Execution Plans to Find Performance Bottlenecks

SQL Server: Fix a Service Broker Queue That Silently Stops Processing Messages Because the Activatio...

SQL Server: Fix a Service Broker Queue That Silently Stops Processing Messages Because the Activatio...

SQL: Use CTEs (Common Table Expressions) for Readable Queries

SQL: Use CTEs (Common Table Expressions) for Readable Queries

Views: 2

Post navigation

CSS: Fix position: sticky Refusing to Stick Inside a Scroll Box
HTML5: Fix a Native dialog Losing Focus Return to Its Button

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

November 2026
M T W T F S S
 1
2345678
9101112131415
16171819202122
23242526272829
30  
« Oct    

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,055)
  • Get the User Name and Domain Name from an Email Address in SQL (980)
  • How to add default value for Entity Framework migrations for DateTime and Bool (976)
  • Get the First and Last Word from a String or Sentence in SQL (857)
  • How to select distinct rows in a datatable in C# (852)
  • How to enable, disable and check if Service Broker is enabled on a database in SQL Server (653)
  • Add Constraint to SQL Table to ensure email contains @ (607)
  • Average of all values in a column that are not zero in SQL (569)
  • How to use Map Mode for Vertical Scroll Mode in Visual Studio (546)
  • Find numbers with more than two decimal places in SQL (475)

Recent Posts

  • C#: Fix a ConditionalWeakTable Entry Vanishing Too Soon
  • Docker: Fix a Stale COPY Layer After Editing .dockerignore
  • Photoshop: Fix a Pattern Overlay Scaling Wrong on a High-DPI File
  • SQL Server: Fix a Computed Column Index Skipped by Implicit Cast
  • HTML5: Fix a required Input Inside a Hidden Tab Blocking Submit
  • SQL Server: Fix ROW_NUMBER Ties Reordering Between Identical Runs
  • C#: Fix an Enum HasFlag Returning True for the Zero Value
  • WordPress: Fix Featured Images Vanishing After a CDN Migration
  • Photoshop: Fix Content-Aware Fill Smearing a Repeating Pattern
  • Asp.Net Core: Fix Antiforgery Tokens Rejected Behind a Reverse Proxy

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,055)
  • Get the User Name and Domain Name from an Email Address in SQL (980)
  • How to add default value for Entity Framework migrations for DateTime and Bool (976)
  • Get the First and Last Word from a String or Sentence in SQL (857)
  • How to select distinct rows in a datatable in C# (852)

Recent Posts

  • C#: Fix a ConditionalWeakTable Entry Vanishing Too Soon
  • Docker: Fix a Stale COPY Layer After Editing .dockerignore
  • Photoshop: Fix a Pattern Overlay Scaling Wrong on a High-DPI File
  • SQL Server: Fix a Computed Column Index Skipped by Implicit Cast
  • HTML5: Fix a required Input Inside a Hidden Tab Blocking Submit

Social

  • ErcanOPAK.com
  • GoodReads
  • LetterBoxD
  • Linkedin
  • The Blog
  • Twitter
© 2026 Bits of .NET | Built with Xblog Plus free WordPress theme by wpthemespace.com