A single value that won’t convert — a 25.5 in an integer column, an empty string, a date like 20240230 — and the ETL run aborts mid-import. Anyone who loads text data from upstream systems knows it: the delivery doesn’t honour the agreed interface, and a bare CONVERT throws an exception instead of cleanly logging the offending value. This article describes … Read more
A date from a CSV file lands as text in the database — and suddenly the 2nd of November turns into the 11th of February. These silent misinterpretations are the classic pitfall of type conversion in SQL Server. Anyone who knows CAST, CONVERT, TRY_CAST and TRY_CONVERT together with the style parameter avoids them. The essentials up front: Prerequisite: SQL Server with SSMS; … Read more
If you’ve ever imported a CSV column with mixed date formats into a datetime column, you know: data quality starts with type conversion. SQL Server leaves you alone with style codes the moment the format strays from the documented ones — TRY_CONVERT handles the documented formats, anything else needs a function of your own. What you’ll take away: Prerequisites: SQL Server 2017+ (for TRY_CONVERT styles 23/126), PostgreSQL 12+ for the … Read more
Data quality starts with type conversion — and for bit columns it shows up right at the input value: yes/no information comes out of legacy sources in a wide variety of notations (‘J’, ‘Y’, ‘ON’, ‘1’, ‘x’, ‘-‘, …). SQL Server’s built-in TRY_CONVERT(bit, …) only covers the integer standard plus ‘true’/’false’ — everything else needs a dedicated conversion function. This article describes both: what’s built in, when … Read more
Data quality with floating-point columns is a discipline of its own — and TRY_CONVERT(float, …) has a few quirks that tend to slip past the import path: an empty string becomes 0 (not NULL), a comma as decimal separator yields NULL, and even after a clean conversion 2 + 3.4 – 3.4 – 2 does not return exactly 0 as a float. This article sorts out the … Read more
Anyone who has imported a point-of-sale report with values like ‘1.234,56 €’ from a CSV into a SQL Server database knows the pattern: TRY_CONVERT(money, ‘1,234.56’) yields 1234.5600. Yet TRY_CONVERT(money, ‘1.234,56’) yields NULL. And even when the import runs cleanly: money / 100 * 100 is not necessarily the same as the input value. At a glance — what this article covers: Prerequisite: SQL Server 2017+ (TRIM is used … Read more
Data quality starts with type conversion — and for integer columns, TRY_CONVERT has a few quirks that tend to slip past the import path: an empty string becomes 0 (not NULL), any decimal or thousands separator yields NULL, and a typed decimal is silently truncated — without rounding. This article sorts out the rules, shows the safe conversion pattern, and adds a … Read more
Anyone who has watched a price import turn ‘123.45 €’ into a NULL instead of the expected decimal number knows the drill: TRY_CONVERT(decimal(5, 2), ‘123,45’) returns NULL, because a comma isn’t accepted as a decimal separator. And even with the comma gone, TRY_CONVERT(decimal(5, 2), ‘1234.56’) is also NULL — this time because of one integer digit too many. At a glance: Prerequisite: SQL Server 2017+ (TRIM in … Read more
