SQL Server: TRY_CONVERT Function

TRY_CONVERT function

This SQL Server tutorial explains how to use the TRY_CONVERT function in SQL Server (Transact-SQL) with syntax and examples.


In SQL Server (Transact-SQL), the TRY_CONVERT 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.


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

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

Parameters or Arguments


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.


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


The value to convert to another datatype.


Optional. The format used to convert between datatypes, such as a date format or string format. It can be one of the following values:

Converting datetime to character

Value (without century)Value (with century)Explanation
0100mon dd yyyy hh:miAM/PM (Default)
1101mm/dd/yyyy (US standard)
2102yy.mm.dd (ANSI standard)
3103dd/mm/yy (British/French standard)
4104dd.mm.yy (German standard)
5105dd-mm-yy (Italian standard)
6106dd mon yy
7107Mon dd, yy
9109mon dd yyyy hh:mi:ss:mmmAM/PM
10110mm-dd-yy (USA standard)
11111yy/mm/dd (Japan standard)
12112yymmdd (ISO standard)
13113dd mon yyyy hh:mi:ss:mmm (Europe standard – 24 hour clock)
14114hh:mi:ss:mmm (24 hour clock)
20120yyyy-mm-dd hh:mi:ss (ODBC canonical – 24 hour clock)
21121yyyy-mm-dd hh:mi:ss:mmm (ODBC canonical – 24 hour clock)
 126yyyy-mm-ddThh:mi:ss:mmm (ISO8601 standard)
 127yyyy-mm-ddThh:mi:ss:mmmZ (ISO8601 standard)
 130dd mon yyyy hh:mi:ss:mmmAM/PM (Hijri standard)
 131dd/mm/yy hh:mi:ss:mmmAM/PM (Hijri standard)

Converting float to real

0Maximum 6 digits (Default)
18 digits
216 digits

Converting money to character

0No comma delimiters, 2 digits to the right of decimal (ie: 1234.56)
1Comma delimiters, 2 digits to the right of decimal (ie: 1,234.56)
2No comma delimiters, 4 digits to the right of decimal (ie: 1234.5678)


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

Applies To

The TRY_CONVERT 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


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

For example:

Result: 14
(result is a truncated int value)

SELECT TRY_CONVERT(float, 14.85);
Result: 14.85
(result is returned as a float value and is not truncated)

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

SELECT TRY_CONVERT(varchar, 15.6);
Result: '15.6'
(result is returned as a varchar)

SELECT TRY_CONVERT(varchar(2), 15.6); 
Result: NULL
(result is NULL because conversion failed since the value will not fit in a 2 character varchar)

SELECT TRY_CONVERT(datetime, '2018-09-13');
Result: '2018-09-13 00:00:00.000'
(result is returned as a datetime)

SELECT TRY_CONVERT(varchar, '2018-09-13', 101);
Result: '09/13/2018'
(result is returned as a varchar with a style of 101 - mm/dd/yyyy (US standard) )

This Post Has 2 Comments

Leave a Reply