Try_convert Function in SQL Server

This guide will show you in detail how to use the TRY_CONVERT data type conversion function in SQL Server with specific syntax and examples to better visualize and capture functions.

Describe

The TRY_CONVERT function in SQL Server allows you to convert an expression to any desired data type but can follow a certain format (especially for the date data type). If the conversion fails, TRY_CONVERT will return NULL, otherwise it will return the corresponding conversion value.

Syntax

To use TRY_CONVERT function in SQL Server, we use the following syntax:

 TRY_CONVERT(kieudulieu(do_dai), bieuthuc, dinh_dang) 

Parameters :

• kieudulieu: the new data type name to which the expression will be converted. Probably 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.
• do_dai (optional): data type length for the result of char, varchar, nchar, nvarchar, binary and varbinary.
• bieuthuc: value to convert to another data type, can also be the name of a column in the table or a calculation expression that needs to be converted to a new data type.
• dinh_dang (optional): is a number that specifies the format for converting data from date format to string form. The table below describes some common formats used in the TRY_CONVERT function.

Year format (yy) Year format (yyyy) Display data 0 100 mon dd yyyy hh: miAM / PM (Default) 1 101 mm / dd / yyyy (US standard) 2 102 yy.mm.dd (ANSI standard) 3 103 dd / mm / yy ( British / French standard 4 104 dd.mm.yy (German standard) 5 105 dd-mm-yy (Italian standard) 6 106 dd mon yy 7 107 Mon dd, yy 8 108 hh: mi: ss 9 109 mon dd yyyy hh: mi: ss: mmmAM / PM 10 110 mm-ddyy (USA standard) 11 111 yy / mm / dd (Japan standard) 12 112 yymmdd (ISO standard) 13 113 dd mon yyyy hh: mi: ss: mmm (Europe standard - 24 hour clock) 14 114 hh: mi: ss: mmm (24 hour clock) 20 120 yyyy-mm-dd hh: mi: ss (ODBC canonical - 24 hour clock) 21 121 yyyy-mm-dd hh: mi: ss: mmm (ODBC canonical - 24 hour clock) 126 yyyy-mm-ddThh: mi: ss: mmm (ISO8601 standard) 127 yyyy-mm-ddThh: mi: ss: mmmZ (ISO8601 standard) 130 dd y yyyy hh: mi: ss: mmmAM / PM (Hijri standard) 131 dd / mm / yy hh: mi: ss: mmmAM / PM (Hijri standard)

 SELECT TRY_CONVERT(int, 14.85); 
 Result: 14 
 (k?t qu? là m?t giá tr? int b? c?t ph?n th?p phân) 
 

 SELECT TRY_CONVERT(float, 14.85); 
 Result: 14.85 
 (k?t qu? ???c tr? v? d??i d?ng giá tr? float và không b? c?t ng?n) 
 

 SELECT TRY_CONVERT(float, '14 Main St.'); 
 Result: NULL 
 (k?t qu? là NULL vì không th? chuy?n ??i giá tr? chu?i này thành float) 
 

 SELECT TRY_CONVERT(varchar, 15.6); 
 Result: '15.6' 
 (k?t qu? ???c tr? v? d??i d?ng varchar) 
 

 SELECT TRY_CONVERT(varchar(2), 15.6); 
 Result: NULL 
 (k?t qu? là NULL vì chuy?n ??i không thành công do giá tr? không phù h?p v?i 
 varchar ?? dài 2 ký t?) 
 

 SELECT TRY_CONVERT(datetime, '2018-09-13'); 
 Result: '2018-09-13 00:00:00.000' 
 (k?t qu? ???c tr? v? d??i d?ng datetime) 
 

 SELECT TRY_CONVERT(varchar, '2018-09-13', 101); 
 Result: '09/13/2018' 
 (k?t qu? ???c tr? v? d??i d?ng varchar v?i ki?u 101 - mm/dd/yyyy (tiêu chu?n Hoa K?)) 

Next lesson: Function @@ VERSION in SQL Server