Data Quality // Type Conversion Basics with T-SQL — CAST, CONVERT, TRY_CAST and TRY_CONVERT Compared

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:

• CAST is ANSI-SQL standard and portable. CONVERT (and TRY_CONVERT) are SQL-Server-specific.
• TRY_CAST and TRY_CONVERT return NULL for a non-convertible value instead of a conversion error, which makes them a good fit for fault-tolerant ETL checks.
• The style parameter (only on CONVERT / TRY_CONVERT) controls the format of the conversion — for example, how a text date is interpreted.
• In ETL the rule is: define the input formats (date, number, yes/no, NULL) up front.

Prerequisite: SQL Server with SSMS. The examples are pure T-SQL without a sample database.

This article is part of the series Data Quality in an ETL Process, which presents a design pattern that validates the extracted data, handles it, and excludes bad data from further processing.

Which function when? The following cheat sheet sorts the four functions by purpose:

CAST, CONVERT, TRY_CAST and TRY_CONVERT — An Overview

For type conversion, SQL Server provides four functions: CAST, CONVERT, TRY_CAST and TRY_CONVERT. The syntax of CAST and TRY_CAST, and of CONVERT and TRY_CONVERT, is identical. The difference lies in the error behavior: If a permitted conversion fails (for example because the text does not contain a valid date), TRY_CAST and TRY_CONVERT return NULL instead of a conversion error. An explicitly disallowed conversion such as TRY_CAST(4 AS xml), however, still raises an error even with the TRY_ variants.

The two sections below derive the two central differences between the function pairs in detail: portability and the style parameter.

Difference 1: ANSI-SQL vs. SQL-Server-specific

First, CONVERT (and therefore TRY_CONVERT) is SQL-Server-specific and not part of the ANSI SQL standard. Put differently: CAST is part of the ANSI SQL standard and is therefore supported by the widely used relational database systems, such as Oracle and Postgres. Which conversions are permitted in detail, however, differs from vendor to vendor. An equivalent CONVERT usually does not exist there. TRY_CAST, too, is not part of the ANSI SQL standard — SQL Server introduced it, though some other systems now offer a TRY_CAST variant as well.

Difference 2: The Style Parameter

Second, CONVERT and TRY_CONVERT have a style parameter that CAST and TRY_CAST lack. Depending on the data types involved, it controls the format of the conversion: For text-to-date it defines how the input is read, for date-to-text it determines the output format (style codes also exist for float and money conversions). The most important use case is a date passed as text to CAST or CONVERT. The date 02.11.2024 (German notation) is written quite differently depending on the country. SQL Server addresses these notations via style codes:

With such ambiguous, language-dependent notations the input format must be known unambiguously and taken into account explicitly — via the matching style parameter or an input format that is unambiguous to begin with. The following SELECT statements illustrate the problem:

  1: SET DATEFORMAT mdy;
  2: 
  3: SELECT CAST('02.11.2024' AS date);       -- 2024-02-11
  4: SELECT CAST('11-02-2024' AS date);       -- 2024-11-02
  5: SELECT CAST('2024/11/02' AS date);       -- 2024-11-02
  6: 
  7: SELECT CONVERT(date, '02.11.2024', 104); -- 2024-11-02
  8: SELECT CONVERT(date, '2024/11/02', 111); -- 2024-11-02
  9: SELECT CONVERT(date, '11-02-2024', 110); -- 2024-11-02
 10: 
 11: SELECT CONVERT(date, '02.11.2024', 111);
 12:  -- Error: Conversion failed when converting date and/or
 13:  --         time from character string.

  1: SET DATEFORMAT mdy;
  2: 
  3: SELECT CAST('02.11.2024' AS date);       -- 2024-02-11
  4: SELECT CAST('11-02-2024' AS date);       -- 2024-11-02
  5: SELECT CAST('2024/11/02' AS date);       -- 2024-11-02
  6: 
  7: SELECT CONVERT(date, '02.11.2024', 104); -- 2024-11-02
  8: SELECT CONVERT(date, '2024/11/02', 111); -- 2024-11-02
  9: SELECT CONVERT(date, '11-02-2024', 110); -- 2024-11-02
 10: 
 11: SELECT CONVERT(date, '02.11.2024', 111);
 12:  -- Error: Conversion failed when converting date and/or
 13:  --         time from character string.

The values noted after the comment markers were taken from the result set in SQL Server Management Studio. By default, SSMS renders a date value in the result view in the ISO format yyyy-MM-dd — that is the client’s display form, not the storage format of the data type.

Important: How CAST interprets a text date depends on the session’s DATEFORMAT / LANGUAGE setting. Line 1 therefore explicitly sets the date format to mdy, the default of a standard installation with us_english — this keeps the results reproducible regardless of server configuration and login. Under this setting, CAST misinterprets the German date in line 3: instead of 02.11.2024 the result shows 11.02.2024 (under dmy you would get the correct 02.11.2024, and for other values even a conversion error). That is exactly why you should never rely on the implicit interpretation of a text date: With the correct style parameter 104 in line 7, CONVERT reads a German date reliably.

Wherever the input format can be defined, an unambiguous format is the most robust choice: CAST('20241102' AS date) returns the 2nd of November 2024 regardless of LANGUAGE and DATEFORMAT. Microsoft, too, recommends the unseparated yyyymmdd format as a language-neutral notation for date literals.

What This Means for ETL

For ETL processes, this has two consequences:

• A safe type conversion is often harder than it looks at first glance. This even holds for supposedly simple types like decimal and float.
• For ETL processes that take data from files (CSV, XML, JSON, …), it must be defined exactly which format a date, a number, a yes/no value, a NULL and so on are delivered in.

The follow-up series Data Quality // Safe Type Conversion with T-SQL uses (almost) exclusively TRY_CONVERT for type conversion. The reason is the style parameter, which steers how the input value is interpreted. A NULL result is a failure signal only if the input value itself was not NULL — which is why the follow-up series stores input and output values separately and identifies conversion failures by comparing them.

Anyone who wants to transfer the approach shown here to another database system has to find equivalent functions there or, if necessary, write their own helper functions. The reduced portability that comes with the SQL-Server-specific function is a deliberate trade-off. With CAST, additional development effort would arise and the readability and maintainability of the required T-SQL artifacts could suffer.

In Postgres: CAST Yes, TRY_CONVERT No

If you need the same logic in Postgres, you’ll find part of it again — but not all of it. CAST is ANSI standard in Postgres too and has the same syntax. The shorthand value::type common there, however, is Postgres-specific. Which conversions are permitted in detail can still differ from one system to the next. There is, however, no direct replacement for CONVERT with a style parameter or for TRY_CONVERT / TRY_CAST:

• Date and number formats can be controlled via to_date(text, format) and to_number(text, format) with an explicit format mask, e.g. to_date('02.11.2024', 'DD.MM.YYYY').
• A fault-tolerant conversion that returns NULL instead of an error has to be built yourself. The common approach is a small PL/pgSQL helper function with BEGIN … EXCEPTION … END; that catches the conversion error specifically via its error class and returns NULL (a blanket WHEN others also swallows unintended errors), or an up-front validation of the input value — since Postgres 16, the built-in validation function pg_input_is_valid(text, type) helps with that.

The basic rule stays the same: know the input format, secure the conversion. Only the tool has a different name.

Summary

• For a standard conversion without format control, CAST is the right choice: The function is ANSI-SQL and thus the most portable of the four. Which conversions are permitted, however, remains system-dependent.
• As soon as the input format matters (say, a text date in local notation), the style parameter belongs in the call — that means CONVERT, or fault-tolerant TRY_CONVERT.
• For ETL, TRY_CONVERT is the recommended choice: define the input formats up front and evaluate NULL results instead of letting the run abort with a runtime error. A NULL counts as a failure signal only if the input value itself was not NULL.

FAQ

Related Articles

• Series (Hub): Data Quality in an ETL Process
• The Design Pattern: Safe Type Conversion with T-SQL
• TRY_CONVERT per data type: date, datetime, datetime2, time · decimal, numeric · bigint, int, smallint, tinyint · money, smallmoney · float, real · bit