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 TRY_CONVERT Hiding Bad Dates Instead of Erroring

- 15.11.26 - ErcanOPAK

🗄️ SQL Server: Fix TRY_CONVERT Hiding Bad Dates Instead of Erroring

TRY_CONVERT is designed to return NULL instead of throwing when a conversion fails — useful for intentionally tolerant parsing. Used on an untrusted or dirty data source, though, a genuinely malformed date string and a legitimately missing source value become completely indistinguishable in the result, silently turning data-quality problems into ordinary NULLs that look no different from “this field was simply empty.” A batch of garbage date strings from a bad import can sail through a TRY_CONVERT step with zero errors and zero warnings.

🔎 The Problem

SELECT RawDateText, TRY_CONVERT(date, RawDateText) AS ParsedDate
FROM StagingImport;

RawDateText     ParsedDate
'2026-03-14'    2026-03-14
'13/45/2026'    NULL        -- garbage input, looks identical to a real NULL below
NULL            NULL        -- genuinely missing source value
-- no error, no warning, no way to tell the two NULL rows apart from this result alone

✅ Fix: Separate ‘Failed to Parse’ From ‘Was Already NULL’

  • When NULL already has a legitimate meaning for a column, add a check that flags rows where the SOURCE value was non-null but the conversion still produced NULL — that specific combination is the data-quality signal otherwise thrown away.
  • Reserve TRY_CONVERT/TRY_CAST for genuinely optional, best-effort parsing, and use plain CONVERT/CAST — which throws immediately — for any pipeline step where a malformed value should stop the batch rather than quietly continue.
  • Log or route rows where conversion failed to a side table during an import, instead of letting them merge invisibly into the rest of the successfully converted rows.

🚨 Why This Is Dangerous

  • A reporting query built on TRY_CONVERT-cleaned dates can produce numbers that look entirely reasonable while quietly excluding or miscounting every row whose date was actually garbage, with no indication in the output that silent data loss happened upstream.

TRY_CONVERT keeps your query from crashing on bad data — it says nothing about whether you’ll ever notice the bad data was there at all.

— Database Engineer

Related posts:

SQL Index Exists but Is Ignored

SQL Index Exists but Is Ignored

SQL Server: Fix a Query Reading Uncommitted Data Because READ_COMMITTED_SNAPSHOT Isn't What You Assu...

SQL Server: Fix a Query Reading Uncommitted Data Because READ_COMMITTED_SNAPSHOT Isn't What You Assu...

SQL Server: Fix the Optimizer Choosing a Table Scan Over an Existing Index Because the Column's Low ...

SQL Server: Fix the Optimizer Choosing a Table Scan Over an Existing Index Because the Column's Low ...

Views: 2

Post navigation

The AI Prompt That Spots a Silent Fallback Hiding a Failed Call
C#: Fix a Static Dictionary Race During Lazy First Access

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