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
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.
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
MSCEWI4027
Last updated
Was this helpful?