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