The TRY_CONVERT function converts values from one data type to another.
The format of the converted data type may also be specified.
If the conversion fails, instead of returning an error, a NULL is returned.
This example converts values to other data types.
If the conversion fails, a NULL is returned.
SELECT TRY_CONVERT(INT, '1859') AS '1859',
TRY_CONVERT(INT, '1859.5') AS '1859.5',
TRY_CONVERT(DECIMAL(10, 2), '1859AB') AS '1859AB'
| 1859 | 1859.5 | 1859AB |
|---|---|---|
| 1859 | NULL | NULL |
Syntax of the TRY_CONVERT function.
TRY_CONVERT(data-type [(length)], value [,style])
data-type -- a data type the value will be converted to.
length -- optional, represents the length of the data-type.
value -- a value to be converted to another data type.
style -- optional, a format to be used to the result.
These are the Date and Time styles for TRY_CONVERT.
| Style | Standard | Result |
|---|---|---|
100 |
Datetime and smalldatetime | mon dd yyyy hh:miAM (or PM) |
101 |
US | mm/dd/yyyy |
102 |
ANSI | yyy.mm.dd |
103 |
British/French | dd/mm/yyyy |
104 |
German | dd.mm.yyyy |
105 |
Italian | dd-mm-yyyy |
106 |
-- | dd mon yyyy |
107 |
-- | Mon dd, yyyy |
108 |
-- | hh:mi:ss |
109 |
Default + milliseconds | mon dd yyyy hh:mi:ss:mmmAM (or PM) |
110 |
USA | mm-dd-yyyy |
111 |
Japan | yyyy/mm/dd |
112 |
ISO | yyyymmdd |
113 |
Europe Default + milliseconds | dd mon yyyy hh:mi:ss:mmm (24h) |
114 |
-- | hh:mi:ss:mmm (24h) |
120 |
ODBC canonical | yyyy-mm-dd hh:mi:ss (24h) |
121 |
ODBC canonical (with milliseconds) default for time, date, datetime2, and datetimeoffset | yyyy-mm-dd hh:mi:ss.mmm (24h) |
126 |
ISO8601 | yyyy-mm-ddThh:mi:ss.mmm (no spaces) |
127 |
ISO8601 with time zone Z | yyyy-MM-ddThh:mm:ss.fffZ (no spaces) |
Next are the float and real styles for TRY_CONVERT.
| Style | Result |
|---|---|
0 |
6 digits max. Use scientific notation, when appropriate. |
1 |
8 digits. Always use scientific notation. |
2 |
16 digits. Always use scientific notation. |
Finally, are the money and smallmoney styles for TRY_CONVERT.
| Style | Result |
|---|---|
0 |
No commas and 2 decimals. Example: 28117.26. |
1 |
Commas and 2 decimals. Example: 28,117.26. |
2 |
No commas and 4 decimals. Example: 28117.2693. |
126 |
Equivalent to 2, when converting to char(n) or varchar(n). |
SELECT CASE
WHEN TRY_CONVERT(INT, '1859AB') IS NULL
THEN 'Failed'
ELSE 'Successful'
END AS 'Conversion Status'
| Conversion Status |
|---|
| Failed |
