SQL Server: TRY_CAST Function

In this post explains how to use the TRY_CAST function in SQL Server (Transact-SQL) with syntax and examples.

Description

In SQL Server (Transact-SQL), the TRY_CAST function tries to convert an expression from one datatype to another datatype. If the conversion fails, the function will return NULL. Otherwise, it will return the converted value.

Syntax

The syntax for the TRY_CAST function in SQL Server (Transact-SQL) is:

TRY_CAST( expression AS type [ (length) ] )

Parameters or Arguments

expression

The value to convert to another datatype.

type

The datatype that you wish to convert expression to. It can be one of the following: bigint, int, smallint, tinyint, bit, decimal, numeric, money, smallmoney, float, real, datetime, smalldatetime, char, varchar, text, nchar, nvarchar, ntext, binary, varbinary, or image.

length

Optional. The length of the resulting data type for char, varchar, nchar, nvarchar, binary and varbinary.

Note

• When casting from a float or numeric to an integer, the TRY_CAST function will truncate the result. For other conversions, the TRY_CAST function will round the result.
• See also the CAST, CONVERT, and TRY_CONVERT functions.

Applies To

The TRY_CAST function can be used in the following versions of SQL Server (Transact-SQL):

• SQL Server 2017, SQL Server 2016, SQL Server 2014, SQL Server 2012

Example

Let's look at some SQL Server TRY_CAST function examples and explore how to use the TRY_CAST function in SQL Server (Transact-SQL).

For example:

SELECT TRY_CAST(14.85 AS int);
Output: 14
(result is a truncated int value)

SELECT TRY_CAST(14.85 AS float);
Output: 14.85
(result is returned as a float value and is not truncated)

SELECT TRY_CAST('14 Main St.' AS float);
Output: NULL
(result is NULL because conversion failed since this string value can not be converted to a float)

SELECT TRY_CAST(15.6 AS varchar);
Output: '15.6'
(result is returned as a varchar)

SELECT TRY_CAST(15.6 AS varchar(2)); 
Output: NULL
(result is NULL because conversion failed since the value will not fit in a 2 character varchar)

SELECT TRY_CAST('2018-09-13' AS datetime);
Output: '2018-09-13 00:00:00.000'
(result is returned as a datetime)