SQL Server TRY_CONVERT Function

SQL Server TRY_CONVERT() function converts an expression of one type to the specified type. It returns NULL if cannot convert it.

The TRY_CONVERT() and the CONVERT() functions are similar except when the conversion is unsuccessful TRY_CONVERT() returns a NULL and CONVERT() throws an error. It is recommended to use TRY_CONVERT() over the CONVERT() function.

TRY_CONVERT(data_type(length), expression [,style])

Parameters

data_type: The target data type to which you want to convert the expression. It includes xml, bigint, and sql_variant.

length: Specifies the length of the target data type. This is an optional parameter. The default value is 30.

expression: Any valid expression for which you want to convert the data type.

style: This is an optional integer expression that specifies how the TRY_CONVERT() function will translate the given expression. See the list of styles for data type convertion.

Return Value

Returns the expression converted to the target data type.

Example 1:

In the following example, the TRY_CONVERT() function converts a character type to int.

SELECT TRY_CONVERT (int, '1234') AS Result;

Example 2:

In the following example, the TRY_CONVERT() function converts a string of valid date format to datetime type.

SELECT TRY_CONVERT (datetime, '11/23/2022') AS Result;

Example 3:

The following uses the TRY_CONVERT() to convert a string value to decimal. It returns a NULL because it cannot convert string 1234.56 to decimal(3,2).

SELECT TRY_CONVERT(decimal(3,2), '1234.56') AS Result;

Example 4:

Use TRY_CONVERT() to convert a string to decimal. It returns a NULL.

SELECT TRY_CONVERT(float, 'abcd') AS Result