I am dealing with a legacy database where dates are stored as strings in multiple formats like 'DD/MM/YYYY', 'MM-DD-YYYY', and 'YYYYMMDD'. When I try to cast them to a DATE type, I often get "Conversion failed when converting date and/or time from character string" errors. What are the best T-SQL functions to handle this, and how can I safely manage different regional date settings without crashing my queries?
3 answers
The most robust method in SQL Server is the CONVERT() function because it allows you to specify a style code that matches your input format. For example, CONVERT(DATE, '31/12/2023', 103) handles the British/French format perfectly. If you are using SQL Server 2012 or later, I highly recommend using TRY_CONVERT() or TRY_CAST(). These functions return a NULL value instead of a high-severity error if the conversion fails, which prevents your entire batch or report from crashing due to a single malformed row. For standard 'YYYYMMDD' formats, SQL Server usually handles these implicitly as they are ISO-standard, but being explicit with style 112 is always safer for long-term maintenance.
Using TRY_CONVERT is a lifesaver for data cleaning, but if I have a mix of formats in the same column, is there a way to validate the format before attempting the conversion?
I always use CAST(string AS DATE) for ISO formats like 'YYYY-MM-DD'. It’s simple, ANSI-standard, and works across most SQL dialects without needing style codes.
I agree with Lisa for standard formats, but just be careful with CAST if your strings are 'MM/DD/YYYY', as it can fail depending on the SET DATEFORMAT of the session. CONVERT is usually my "safety first" choice.
You can use a CASE statement combined with ISDATE() or TRY_CONVERT(). For instance, check if TRY_CONVERT(DATE, column, 101) is not null for US format, else try style 103. However, ISDATE() can be tricky because it depends on your server's LANGUAGE and DATEFORMAT settings. A more modern approach is using TRY_PARSE() with the USING culture clause, like TRY_PARSE('12/05/2023' AS DATE USING 'en-GB'), which is very explicit and ignores server-wide regional settings.