Data Quality

Data quality in SQL Server // TRY_CONVERT for date, datetime, datetime2 and time done safely

by

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 in SQL Server // TRY_CONVERT for bit done safely — converting yes/no values

by

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

TRY_CONVERT // Converting data to money, smallmoney

by

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 in SQL Server // TRY_CONVERT for bigint, int, smallint and tinyint done safely

by

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

Data quality in SQL Server // TRY_CONVERT for decimal and numeric done safely

by

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