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

Published Jun 30, 2026
by

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 don’t throw an exception — they return NULL on failure, which is ideal for ETL.
  • The style parameter (only on CONVERT / TRY_CONVERT) decides 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 — the sections below derive the details:

TaskFunction
Standard conversion, portableCAST
Steer the format via a styleCONVERT
Fault-tolerant (NULL instead of an exception)TRY_CAST
Fault-tolerant and style-drivenTRY_CONVERT

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 only difference: TRY_CAST and TRY_CONVERT don’t throw an exception for a non-convertible value; they return NULL on failure.

There are two key differences between CAST and CONVERT.

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 available in practically every relational database system (Oracle, Postgres and others) — though which conversions are permitted 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

econd, CONVERT and TRY_CONVERT — unlike CAST and TRY_CAST — have a style parameter that lets you steer how SQL Server interprets the supplied value. A typical example is a date passed as text to CAST or CONVERT. Dates are written very differently around the world. The date 02.11.2024 (German notation) is written quite differently depending on the country:

CountryFormat stringDateStyle parameter
Germanydd.mm.yyyy02.11.2024104
USAmm-dd-yyyy11-02-2024110
Japanyyyy/mm/dd2024/11/02111

A reliable conversion is only possible if we know the date format and the correct style parameter. The following SELECT statements illustrate the problem:

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

The values noted after the comment markers were taken from the result set in SQL Server Management Studio; SSMS renders a date value in the ISO format yyyy-MM-dd by default. Important: how CAST interprets a textual date string depends on the session’s LANGUAGE / DATEFORMAT setting — the results shown here are for the default session (us_english, i.e. mdy). Under that setting, CAST misinterprets the German date in line 1: instead of 02.11.2024 the result is 11.02.2024 (under dmy you’d get the correct 02.11.2024, and for other values even a conversion error). That’s exactly why you should never rely on the implicit interpretation of textual dates — with the correct style parameter 104 in line 5, CONVERT reads a German date reliably.

What This Means for ETL

Without going deeper here into the type-specific problems of conversion, two things should be clear:

  • A safe type conversion can be a challenging affair — this even holds for supposedly simple types like decimal and float.
  • When building an ETL process whose data is extracted from files (CSV, XML, JSON, …), it is essential to specify 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 — precisely because the style parameter lets you steer how the input value is interpreted.

If the approach shown here is to be ported to another database system, equivalent functions have to be found or, if necessary, written yourself. Using a non-ANSI-SQL function does hamper portability; with CAST, however, 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 (or the shorthand value::type) is ANSI standard in Postgres too and has the same syntax — though which conversions are permitted can 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 are 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 (NULL instead of an exception) is not built in. The common approach is a small PL/pgSQL helper function with BEGIN … EXCEPTION WHEN others THEN RETURN NULL; END; or an up-front validation of the input value.

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

FAQ

CAST or CONVERT — which should I use?

CAST is ANSI-SQL and portable — the first choice when no format control is needed. You need CONVERT whenever you have to steer, via the style parameter, how a text (especially a date) is interpreted. In an ETL context there is therefore usually no way around CONVERT or TRY_CONVERT.

Why does TRY_CONVERT return NULL instead of an error?

That’s exactly the point: TRY_CONVERT and TRY_CAST don’t abort processing with an exception — they return NULL for values that can’t be converted. This lets you detect and route out bad records in an ETL run instead of having the whole batch fail.

What is the style parameter for?

The style parameter (the third argument of CONVERT / TRY_CONVERT) defines the schema by which a text is interpreted — for example 104 for the German date format dd.mm.yyyy. Without the right style, SQL Server interprets a text date depending on the session’s LANGUAGE / DATEFORMAT setting — and easily returns a wrong result (see line 1 in the example above).

Does this work in Postgres too?

Partly — see the section “In Postgres”. CAST yes, CONVERT / TRY_CONVERT no; format control there runs via to_date / to_number, and a fault-tolerant conversion via your own helper function.