SSC-FDM-0013

Timezone expression could not be mapped

Description

This FMD message is added to indicate scenarios where the actual value of a timezone expression cannot be determined, and therefore, the translated results might be different. When the timezone value used is a literal string, SnowConvert can take it and map it to its corresponding timezone value in Snowflake. However, when this value is specified by an expression, SnowConvert cannot get the timezone value that will be used at runtime and, therefore, cannot map this value to its corresponding Snowflake equivalent.

Example Code

Input Code (Oracle)

IN -> Oracle_01.sql

SELECT TIMESTAMP '1998-12-25 09:26:50.12' AT TIME ZONE SESSIONTIMEZONE FROM DUAL;
SELECT TIMESTAMP '1998-12-25 09:26:50.12' AT TIME ZONE Expression FROM DUAL;

Output Code

OUT -> Oracle_01.sql

SELECT
--** SSC-FDM-0013 - TIMEZONE EXPRESSION COULD NOT BE MAPPED, RESULTS MAY BE DIFFERENT **
TO_TIMESTAMP_LTZ( TIMESTAMP '1998-12-25 09:26:50.12')
FROM DUAL;

SELECT
--** SSC-FDM-0013 - TIMEZONE EXPRESSION COULD NOT BE MAPPED, RESULTS MAY BE DIFFERENT **
CONVERT_TIMEZONE(Expression, TIMESTAMP '1998-12-25 09:26:50.12')
FROM DUAL;

Input Code (Teradata)

IN -> Teradata_01.sql

select TIMESTAMP '1998-12-25 09:26:50.12' AT TIME ZONE SESSIONTIMEZONE;
select current_timestamp at time zone CONCAT(' America ', ' Pacific'); 
select current_timestamp at time zone (SELECT COL1 FROM TABLE1 WHERE COL2 = 2);

Output Code

OUT -> Teradata_01.sql

SELECT
CONVERT_TIMEZONE(SESSIONTIMEZONE, TIMESTAMP '1998-12-25 09:26:50.12') /*** SSC-FDM-0013 - TIMEZONE EXPRESSION COULD NOT BE MAPPED, RESULTS MAY BE DIFFERENT ***/;

SELECT
CONVERT_TIMEZONE(CONCAT(' America ', ' Pacific'), CURRENT_TIMESTAMP) /*** SSC-FDM-0013 - TIMEZONE EXPRESSION COULD NOT BE MAPPED, RESULTS MAY BE DIFFERENT ***/;

SELECT
CONVERT_TIMEZONE(
                  !!!RESOLVE EWI!!! /*** SSC-EWI-0108 - THE FOLLOWING SUBQUERY MATCHES AT LEAST ONE OF THE PATTERNS CONSIDERED INVALID AND MAY PRODUCE COMPILATION ERRORS ***/!!!
                  (
                  SELECT
                  COL1 FROM
                  TABLE1
                  WHERE COL2 = 2), CURRENT_TIMESTAMP) /*** SSC-FDM-0013 - TIMEZONE EXPRESSION COULD NOT BE MAPPED, RESULTS MAY BE DIFFERENT ***/;

Timezone Compatibility in Oracle

The majority of timezone name expressions in Oracle are directly supported in Snowflake, when this is the case, the migration will run without issues. Additionally, here is a list of which ones are not supported by Snowflake at the moment, and therefore will include the functional difference message.

• Africa/Doula

• Asia/Ulaanbaator

• Asia/Yetaterinburg

• Canada/East-Saskatchewan

• CST

• PST

• US/Pacific-New

Recommendations

• No end-user action is required.

• If you need more support, you can email us at [e-mail]