TRY_CONVERT
Description
Returns a value cast to the specified data type if the cast succeeds; otherwise, returns null.
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
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
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
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
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.
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" }
Last updated