TRY_CONVERT

Description

Returns a value cast to the specified data type if the cast succeeds; otherwise, returns null.
(SQL Server Language Reference TRY_CONVERT)

Syntax

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

Source Patterns

Basic Transformation

In order to transform this function, we have to check the parameters of the TRY_CONVERT first.

TRY_CONVERT( INT, 'test')

If the expression that needs to be casted is a string, it will be transfomed to TRY_CAST, which is a function of Snowflake.

TRY_CAST( 'test' AS INT)

TRY_CAST

The TRY_CAST shares the same transformation with TRY_CONVERT.

Example

SELECT TRY_CAST('12345' AS NUMERIC) NUMERIC_RESULT,
 TRY_CAST('123.45' AS DECIMAL(20,2)) DECIMAL_RESULT,
 TRY_CAST('123' AS INT) INT_RESULT,
 TRY_CAST('123.02' AS FLOAT) FLOAT_RESULT,
 TRY_CAST('123.02' AS DOUBLE PRECISION) DOUBLE_PRECISION_RESULT,

 TRY_CAST('2017-01-01 12:00:00' AS DATE) DATE_RESULT,
 TRY_CAST('2017-01-01 12:00:00' AS DATETIME) DATETIME_RESULT,
 TRY_CAST('2017-01-01 12:00:00' AS SMALLDATETIME) SMALLDATETIME_RESULT,
 TRY_CAST('12:00:00' AS TIME) TIME_RESULT,
 TRY_CAST('2017-01-01 12:00:00' AS TIMESTAMP) TIMESTAMP_RESULT,
 TRY_CAST('2017-01-01 12:00:00' AS DATETIMEOFFSET) DATETIMEOFFSET_RESULT,

 TRY_CAST(1234 AS VARCHAR) VARCHAR_RESULT,
 TRY_CAST(1 AS CHAR) CHAR_RESULT,
 TRY_CAST('2017-01-01 12:00:00' AS SQL_VARIANT) SQL_VARIANT_RESULT,
 TRY_CAST('LINESTRING(-122.360 47.656, -122.343 47.656 )' AS GEOGRAPHY) GEOGRAPHY_RESULT;

The result will be the same with the example of TRY_CONVERT, the only difference would be in the warning message.

SELECT TRY_CAST('12345' AS NUMERIC) NUMERIC_RESULT,
TRY_CAST('123.45' AS DECIMAL(20,2)) DECIMAL_RESULT,
TRY_CAST('123' AS INT) INT_RESULT,
TRY_CAST('123.02' AS FLOAT) FLOAT_RESULT,
TRY_CAST('123.02' AS DOUBLE PRECISION) DOUBLE_PRECISION_RESULT,
TRY_CAST('2017-01-01 12:00:00' AS DATE) DATE_RESULT,
TRY_CAST('2017-01-01 12:00:00' AS DATETIME) DATETIME_RESULT,
TRY_CAST('2017-01-01 12:00:00' AS DATETIME) SMALLDATETIME_RESULT,
TRY_CAST('12:00:00' AS TIME) TIME_RESULT,
TRY_CAST('2017-01-01 12:00:00' AS TIMESTAMP) TIMESTAMP_RESULT,
TRY_CAST('2017-01-01 12:00:00' AS TIMESTAMP_LTZ) DATETIMEOFFSET_RESULT,
TO_VARCHAR(1234) /*** MSC-WARNING - MSCEWI4027 - FUNCTION TRY_CAST TRANSFORMED TO TO_VARCHAR ***/ VARCHAR_RESULT,
TO_CHAR(1) /*** MSC-WARNING - MSCEWI4027 - FUNCTION TRY_CAST TRANSFORMED TO TO_CHAR ***/ CHAR_RESULT,
TO_VARIANT('2017-01-01 12:00:00') /*** MSC-WARNING - MSCEWI4027 - FUNCTION TRY_CAST TRANSFORMED TO TO_VARIANT ***/ SQL_VARIANT_RESULT,
TO_GEOGRAPHY('LINESTRING(-122.360 47.656, -122.343 47.656 )') /*** MSC-WARNING - MSCEWI4027 - FUNCTION TRY_CAST TRANSFORMED TO TO_GEOGRAPHY ***/ GEOGRAPHY_RESULT;

Known Issues

If the data type is Varchar or Char, then it will be transformed differently.

TRY_CONVERT(VARCHAR, 1234)
TRY_CONVERT(CHAR, 1)

If TRY_CAST is used with VARCHAR or CHAR in Snowflake, it will cause an error, so it will be transformed to

TO_VARCHAR( 1234) /*** MSC-WARNING - MSCEWI4027 - FUNCTION TRY_CONVERT TRANSFORMED TO TO_VARCHAR ***/
TO_CHAR( 1) /*** MSC-WARNING - MSCEWI4027 - FUNCTION TRY_CONVERT TRANSFORMED TO TO_CHAR ***/

The same happens with the data types of SQL_VARIANT and GEOGRAPHY.

TRY_CONVERT(SQL_VARIANT, '2017-01-01 12:00:00')
TRY_CONVERT(GEOGRAPHY, 'LINESTRING(-122.360 47.656, -122.343 47.656 )')

Are transformed to

TO_VARIANT( '2017-01-01 12:00:00') /*** MSC-WARNING - MSCEWI4027 - FUNCTION TRY_CONVERT TRANSFORMED TO TO_VARIANT ***/
TO_GEOGRAPHY( 'LINESTRING(-122.360 47.656, -122.343 47.656 )') /*** MSC-WARNING - MSCEWI4027 - FUNCTION TRY_CONVERT TRANSFORMED TO TO_GEOGRAPHY ***/

If the expression is not a string, there is a very high chance that it will fail, since the TRY_CAST of snowflake works only with string expressions.

In this case, another transformation will be done

TRY_CAST(14.85 AS INT)

Will be transformed to

CAST(14.85 AS INT) /*** MSC-WARNING - MSCEWI4027 - FUNCTION TRY_CAST TRANSFORMED TO CAST ***/

Now, with these transformation, there could be problems depending on what is being done with the functions. The TRY_CONVERT of SqlServer returns nulls if the convertion was not possible.

This can be used to do logic like this

SELECT 
    CASE
        WHEN TRY_CONVERT( INT, 'Expression') IS NULL
        THEN 'FAILED'
        ELSE 'SUCCEDDED'
    END;

That type of conditions with the TRY_CONVERT can be used with the TRY_CAST, but what happens if it is transformed to TO_VARCHAR, TOCHAR or to the CAST? If the convertion in those functions fails, it will cause an error instead of just returning null.

Examples

In this sample we have several TRY_CONVERT with different data types

SELECT TRY_CONVERT(NUMERIC, '12345') NUMERIC_RESULT,
 TRY_CONVERT(DECIMAL(20,2), '123.45') DECIMAL_RESULT,
 TRY_CONVERT(INT, '123') INT_RESULT,
 TRY_CONVERT(FLOAT, '123.02') FLOAT_RESULT,
 TRY_CONVERT(DOUBLE PRECISION, '123.02') DOUBLE_PRECISION_RESULT,

 TRY_CONVERT(DATE, '2017-01-01 12:00:00') DATE_RESULT,
 TRY_CONVERT(DATETIME, '2017-01-01 12:00:00') DATETIME_RESULT,
 TRY_CONVERT(SMALLDATETIME, '2017-01-01 12:00:00') SMALLDATETIME_RESULT,
 TRY_CONVERT(TIME, '12:00:00') TIME_RESULT,
 TRY_CONVERT(TIMESTAMP, '2017-01-01 12:00:00') TIMESTAMP_RESULT,
 TRY_CONVERT(DATETIMEOFFSET, '2017-01-01 12:00:00') DATETIMEOFFSET_RESULT,

 TRY_CONVERT(VARCHAR, 1234) VARCHAR_RESULT,
 TRY_CONVERT(CHAR, 1) CHAR_RESULT,
 TRY_CONVERT(SQL_VARIANT, '2017-01-01 12:00:00') SQL_VARIANT_RESULT,
 TRY_CONVERT(GEOGRAPHY, 'LINESTRING(-122.360 47.656, -122.343 47.656 )') GEOGRAPHY_RESULT;

If we migrate that select, we will get the following result

SELECT TRY_CAST( '12345' AS NUMERIC) NUMERIC_RESULT,
TRY_CAST( '123.45' AS DECIMAL(20,2)) DECIMAL_RESULT,
TRY_CAST( '123' AS INT) INT_RESULT,
TRY_CAST( '123.02' AS FLOAT) FLOAT_RESULT,
TRY_CAST( '123.02' AS DOUBLE PRECISION) DOUBLE_PRECISION_RESULT,
TRY_CAST( '2017-01-01 12:00:00' AS DATE) DATE_RESULT,
TRY_CAST( '2017-01-01 12:00:00' AS DATETIME) DATETIME_RESULT,
TRY_CAST( '2017-01-01 12:00:00' AS DATETIME) SMALLDATETIME_RESULT,
TRY_CAST( '12:00:00' AS TIME) TIME_RESULT,
TRY_CAST( '2017-01-01 12:00:00' AS TIMESTAMP) TIMESTAMP_RESULT,
TRY_CAST( '2017-01-01 12:00:00' AS TIMESTAMP_LTZ) DATETIMEOFFSET_RESULT,
TO_VARCHAR( 1234) /*** MSC-WARNING - MSCEWI4027 - FUNCTION TRY_CONVERT TRANSFORMED TO TO_VARCHAR ***/ VARCHAR_RESULT,
TO_CHAR( 1) /*** MSC-WARNING - MSCEWI4027 - FUNCTION TRY_CONVERT TRANSFORMED TO TO_CHAR ***/ CHAR_RESULT,
TO_VARIANT( '2017-01-01 12:00:00') /*** MSC-WARNING - MSCEWI4027 - FUNCTION TRY_CONVERT TRANSFORMED TO TO_VARIANT ***/ SQL_VARIANT_RESULT,
TO_GEOGRAPHY( 'LINESTRING(-122.360 47.656, -122.343 47.656 )') /*** MSC-WARNING - MSCEWI4027 - FUNCTION TRY_CONVERT TRANSFORMED TO TO_GEOGRAPHY ***/ GEOGRAPHY_RESULT;

Let's execute each one and compare the result.

Alias	SqlServer Result	Snowflake Result
NUMERIC_RESULT	12345	12345
DECIMAL_RESULT	123.45	123.45
INT_RESULT	123	123
FLOAT_RESULT	123.02	123.02
DOUBLE_PRECISION_RESULT	123.02	123.02
DATE_RESULT	2017-01-01	2017-01-01
DATETIME_RESULT	2017-01-01 12:00:00.000	2017-01-01 12:00:00.000
SMALLDATETIME_RESULT	2017-01-01 12:00:00	2017-01-01 12:00:00.000
TIME_RESULT	12:00:00.0000000	12:00:00
TIMESTAMP_RESULT	0x323031372D30312D	2017-01-01 12:00:00.000
DATETIMEOFFSET_RESULT	2017-01-01 12:00:00.0000000 +00:00	2017-01-01 12:00:00.000 -0800
VARCHAR_RESULT	1234	1234
CHAR_RESULT	1	1
SQL_VARIANT_RESULT	2017-01-01 12:00:00	"2017-01-01 12:00:00"
GEOGRAPHY_RESULT	0xE610000001148716D9CEF7D34740D7A3703D0A975EC08716D9CEF7D34740CBA145B6F3955EC0	{ "coordinates": [ [ -122.36, 47.656 ], [ -122.343, 47.656 ] ], "type": "LineString" }

Alias

SqlServer Result

Snowflake Result

NUMERIC_RESULT

12345

DECIMAL_RESULT

123.45

INT_RESULT

123

FLOAT_RESULT

123.02

DOUBLE_PRECISION_RESULT

123.02

DATE_RESULT

2017-01-01

DATETIME_RESULT

2017-01-01 12:00:00.000

SMALLDATETIME_RESULT

2017-01-01 12:00:00

2017-01-01 12:00:00.000

TIME_RESULT

12:00:00.0000000

12:00:00

TIMESTAMP_RESULT

0x323031372D30312D

2017-01-01 12:00:00.000

DATETIMEOFFSET_RESULT

2017-01-01 12:00:00.0000000 +00:00

2017-01-01 12:00:00.000 -0800

VARCHAR_RESULT

1234

CHAR_RESULT

SQL_VARIANT_RESULT

2017-01-01 12:00:00

"2017-01-01 12:00:00"

GEOGRAPHY_RESULT

0xE610000001148716D9CEF7D34740D7A3703D0A975EC08716D9CEF7D34740CBA145B6F3955EC0

{ "coordinates": [ [ -122.36, 47.656 ], [ -122.343, 47.656 ] ], "type": "LineString" }

PreviousCONVERT NextData Type functions

Last updated 2 years ago

Description

Syntax

Source Patterns

Basic Transformation

TRY_CAST

Example

Known Issues

Examples

Related Ewis