CAST vs CONVERT vs TRY_CONVERT

CAST(), CONVERT(), and TRY_CONVERT() in SQL Server

All three functions convert a value from one data type to another. The main differences are standard compatibility, formatting support, and error handling.

1. CAST()

CAST() is the standard SQL conversion function.

Syntax

CAST(expression AS data_type)

Example

SELECT CAST('125' AS INT) AS NumberValue;

Result:

125

Use CAST() for ordinary conversions, especially when portable SQL is preferred.

SELECT CAST(125.75 AS INT); -- 125

Converting a decimal value to an integer removes the fractional portion.

Invalid conversion

SELECT CAST('ABC' AS INT);

This raises a conversion error and can terminate the statement.

2. CONVERT()

CONVERT() is specific to SQL Server. It supports a style parameter, mainly for date, time and binary formatting.

Syntax

CONVERT(data_type, expression [, style])

Example

SELECT CONVERT(INT, '125') AS NumberValue;

Date formatting

SELECT CONVERT(VARCHAR(10), GETDATE(), 103);

Style 103 returns the British/French date format:

17/09/2026

Some common date styles:

Invalid conversions raise an error:

SELECT CONVERT(INT, 'ABC');

3. TRY_CONVERT()

TRY_CONVERT() works like CONVERT(), but returns NULL when the value cannot be converted.

Syntax

TRY_CONVERT(data_type, expression [, style])

Example

SELECT TRY_CONVERT(INT, 'ABC') AS NumberValue;

Result:

NULL

This is useful when importing or validating data that may contain invalid values.

SELECT *
FROM ImportedData
WHERE TRY_CONVERT(DECIMAL(10,2), AmountText) IS NULL;

This identifies rows containing invalid numeric values.

Date validation

SELECT TRY_CONVERT(DATE, '31/12/2026', 103);

Result:

2026-12-31

Important limitation

TRY_CONVERT() returns NULL for conversion failures, but it can still raise an error when a conversion is explicitly not allowed.

SELECT TRY_CONVERT(XML, 100);

This conversion is not permitted, so SQL Server raises an error.

Also, a NULL result does not always mean conversion failed—the original input might already have been NULL.

Simple comparison

SELECT CAST('100' AS INT);          -- 100
SELECT CONVERT(INT, '100');         -- 100
SELECT TRY_CONVERT(INT, 'ABC');     -- NULL

Performance consideration

Avoid applying conversion functions directly to indexed columns:

-- May prevent an Index Seek
WHERE CONVERT(VARCHAR(10), OrderDate, 112) = '20260917'

Prefer a range condition:

WHERE OrderDate >= '20260917'
  AND OrderDate <  '20260918'

Key interview points

• CAST() is ANSI SQL standard and more portable.

• CONVERT() is SQL Server-specific and supports formatting styles.

• CAST() and CONVERT() raise errors when conversion fails.

• TRY_CONVERT() returns NULL for most failed conversions.

• Use TRY_CONVERT() for data validation and unreliable imported data.

• TRY_CAST() is also available and behaves similarly to TRY_CONVERT() without the style parameter.

• Do not silently accept NULL from TRY_CONVERT() without validating or logging bad data.

• Applying conversion functions to indexed columns can negatively affect index usage.