Converting types: CAST, CONVERT and TRY_CAST

Every value has a type, and combining values of different types needs a conversion. Sometimes SQL Server does it silently (implicit conversion); sometimes you must ask for it (explicit conversion, with CAST or CONVERT). Knowing which happens, and in which direction, avoids a whole class of bugs.

Data type precedence

When an operator meets two different types, the one with lower precedence is converted to the one with higher precedence. Numbers outrank text, so + between text and an int is addition, not joining:

1SELECT '10' + 5    AS TextPlusInt,2       '10' + '5'  AS TextPlusText,3       CONCAT('10', 5) AS Joined,4       10 + 2.5    AS IntPlusDecimal;

TextPlusInt  TextPlusText  Joined  IntPlusDecimal
-----------  ------------  ------  --------------
         15  105           105               12.5

So 'abc' + 5 fails: SQL Server tries to turn 'abc' into an int. And comparing a varchar column with an int value converts the column, row by row, which is both slow and a source of conversion errors.

CAST and CONVERT

CAST(x AS type) is standard SQL. CONVERT(type, x [, style]) is T-SQL’s version, with an optional style number that controls how dates and numbers are written as text or read from it:

1SELECT CAST('42' AS int) AS CastText,2       CAST(18.5 AS varchar(10)) AS NumberAsText,3       CONVERT(varchar(10), CAST('2024-12-31' AS date), 103) AS Style103,4       CONVERT(varchar(8),  CAST('2024-12-31' AS date), 112) AS Style112,5       CONVERT(date, '31/12/2024', 103) AS ReadBritish;

CastText  NumberAsText  Style103    Style112  ReadBritish
--------  ------------  ----------  --------  -----------
      42  18.5          31/12/2024  20241231  2024-12-31

TRY_CAST: convert or give NULL

CAST stops the whole query at the first value it cannot convert. TRY_CAST and TRY_CONVERT return NULL instead, which makes them ideal for checking imported data. Explore which texts convert to which types:

A typical use: a spreadsheet was imported as text, and you need to find the rows that will not convert before loading them into a real table:

1CREATE TABLE #Import (RowNo int, PriceText varchar(20), DateText varchar(20));2INSERT INTO #Import (RowNo, PriceText, DateText) VALUES3    (1, '12.50', '2024-07-01'),4    (2, ' 7',    '2024-07-02'),5    (3, 'n/a',   '2024-07-03'),6    (4, '9.99',  '2024-07-32'),7    (5, '',      '');8 9SELECT RowNo, PriceText, DateText,10       TRY_CAST(PriceText AS decimal(10,2)) AS Price,11       TRY_CAST(DateText AS date) AS TheDate12FROM #Import13WHERE TRY_CAST(PriceText AS decimal(10,2)) IS NULL14   OR TRY_CAST(DateText AS date) IS NULL;

RowNo  PriceText  DateText    Price  TheDate
-----  ---------  ----------  -----  ----------
    3  n/a        2024-07-03   NULL  2024-07-03
    4  9.99       2024-07-32   9.99  NULL
    5                          NULL  1900-01-01

The empty-string trap

Look again at row 5 above. It was caught only because its blank price failed to become a decimal; its blank date became 1 January 1900 without complaint. An empty string converts to 0 as an int and to 1900-01-01 as a date, so blank cells in an import can quietly become real-looking values:

1SELECT CAST('' AS int) AS EmptyAsInt,2       CAST('' AS date) AS EmptyAsDate,3       TRY_CAST(NULLIF(TRIM(''), '') AS date) AS Safe;

EmptyAsInt  EmptyAsDate  Safe
----------  -----------  ----
         0  1900-01-01   NULL

NULLIF(TRIM(x), '') turns blanks (and all-space values) into NULL first, so they stay unknown instead of becoming 0 or 1900-01-01.

FORMAT for display

FORMAT(value, pattern, culture) uses .NET format strings, with a culture for currency symbols and separators. Use it for display only:

1SELECT Name,2       FORMAT(Price, 'C', 'en-GB') AS UkPrice,3       FORMAT(Price, 'C', 'de-DE') AS GermanPrice,4       FORMAT(Stock, 'N0') AS StockWithSeparators5FROM dbo.Products6WHERE ProductId IN (2, 6);

Name              UkPrice  GermanPrice  StockWithSeparators
----------------  -------  -----------  -------------------
Cordless drill    £89.00   89,00 €      12
Timber 2x4 2.4 m  £6.80    6,80 €       500

1SELECT PriceText,2       TRY_CAST(NULLIF(TRIM(PriceText), '') AS decimal(10,2)) AS Price3FROM (VALUES ('12.50'), (' 7'), ('n/a'), (''), ('   ')) AS v(PriceText);

PriceText  Price
---------  -----
12.50      12.50
 7          7.00
n/a         NULL
            NULL
            NULL