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

Sql Server

IN -> SqlServer_01.sql

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.

Snowflake

OUT -> SqlServer_01.sql

SELECT
 TRY_CAST('12345' AS NUMERIC(38, 18)) 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 TIMESTAMP_NTZ(3)) DATETIME_RESULT,
 TRY_CAST('2017-01-01 12:00:00' AS TIMESTAMP_NTZ(0)) SMALLDATETIME_RESULT,
 TRY_CAST('12:00:00' AS TIME(7)) TIME_RESULT,
 TRY_CAST('2017-01-01 12:00:00' AS TIMESTAMP) TIMESTAMP_RESULT,
 TRY_CAST('2017-01-01 12:00:00' AS TIMESTAMP_TZ(7)) DATETIMEOFFSET_RESULT,
 TO_VARCHAR(1234) VARCHAR_RESULT,
 TO_CHAR(1) CHAR_RESULT,
 TRY_CAST('2017-01-01 12:00:00' AS VARIANT) SQL_VARIANT_RESULT,
 TRY_CAST('LINESTRING(-122.360 47.656, -122.343 47.656 )' AS 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);
TO_CHAR(1);

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');
TO_GEOGRAPHY('LINESTRING(-122.360 47.656, -122.343 47.656 )');

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) /*** SSC-FDM-TS0005 - TRY_CONVERT/TRY_CAST COULD NOT BE CONVERTED TO TRY_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

Sql Server

IN -> SqlServer_02.sql

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

Snowflake

OUT -> SqlServer_02.sql

SELECT
 CAST('12345' AS NUMERIC(38, 18)) /*** SSC-FDM-TS0005 - TRY_CONVERT/TRY_CAST COULD NOT BE CONVERTED TO TRY_CAST ***/ NUMERIC_RESULT,
 CAST('123.45' AS DECIMAL(20,2)) /*** SSC-FDM-TS0005 - TRY_CONVERT/TRY_CAST COULD NOT BE CONVERTED TO TRY_CAST ***/ DECIMAL_RESULT,
 CAST('123' AS INT) /*** SSC-FDM-TS0005 - TRY_CONVERT/TRY_CAST COULD NOT BE CONVERTED TO TRY_CAST ***/ INT_RESULT,
 CAST('123.02' AS FLOAT) /*** SSC-FDM-TS0005 - TRY_CONVERT/TRY_CAST COULD NOT BE CONVERTED TO TRY_CAST ***/ FLOAT_RESULT,
 CAST('123.02' AS DOUBLE PRECISION) /*** SSC-FDM-TS0005 - TRY_CONVERT/TRY_CAST COULD NOT BE CONVERTED TO TRY_CAST ***/ DOUBLE_PRECISION_RESULT,
 CAST('2017-01-01 12:00:00' AS DATE) /*** SSC-FDM-TS0005 - TRY_CONVERT/TRY_CAST COULD NOT BE CONVERTED TO TRY_CAST ***/ DATE_RESULT,
 CAST('2017-01-01 12:00:00' AS TIMESTAMP_NTZ(3)) /*** SSC-FDM-TS0005 - TRY_CONVERT/TRY_CAST COULD NOT BE CONVERTED TO TRY_CAST ***/ DATETIME_RESULT,
 CAST('2017-01-01 12:00:00' AS TIMESTAMP_NTZ(0)) /*** SSC-FDM-TS0005 - TRY_CONVERT/TRY_CAST COULD NOT BE CONVERTED TO TRY_CAST ***/ SMALLDATETIME_RESULT,
 CAST('12:00:00' AS TIME(7)) /*** SSC-FDM-TS0005 - TRY_CONVERT/TRY_CAST COULD NOT BE CONVERTED TO TRY_CAST ***/ TIME_RESULT,
 CAST('2017-01-01 12:00:00' AS TIMESTAMP) /*** SSC-FDM-TS0005 - TRY_CONVERT/TRY_CAST COULD NOT BE CONVERTED TO TRY_CAST ***/ TIMESTAMP_RESULT,
 CAST('2017-01-01 12:00:00' AS TIMESTAMP_TZ(7)) /*** SSC-FDM-TS0005 - TRY_CONVERT/TRY_CAST COULD NOT BE CONVERTED TO TRY_CAST ***/ DATETIMEOFFSET_RESULT,
 TO_VARCHAR(1234) VARCHAR_RESULT,
 TO_CHAR(1) CHAR_RESULT,
 TO_VARIANT('2017-01-01 12:00:00') SQL_VARIANT_RESULT,
 TO_GEOGRAPHY('LINESTRING(-122.360 47.656, -122.343 47.656 )') 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" }

Related EWIs

1. SSC-FDM-TS0005: TRY_CONVERT/TRY_CAST could not be converted to TRY_CAST.