SQL TRY_CONVERT Function

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.

Example

#

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'
Result:  1 record
1859 1859.5 1859AB
1859 NULL NULL

Syntax

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).

More Examples

TRY_CONVERT with CASE

Problem: Convert the value to integer. If the conversion succeeds, return 'Successful', if not, return 'Failed'.
SELECT CASE
         WHEN TRY_CONVERT(INT, '1859AB') IS NULL
         THEN 'Failed'
         ELSE 'Successful'
       END AS 'Conversion Status'
Result:  1 record
Conversion Status
Failed

You may also like



Guides


vsn 3.1