🗄️ 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_CASTfor genuinely optional, best-effort parsing, and use plainCONVERT/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.
