MSCEWI3117

Day format works differently between Oracle and Snowflake.

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

Severity

Low

Description

This warning is added when TRUNC function is used with DAY, DY, or D formats.

By default, Oracle's day formats work as the first day of the week (which is Sunday by default and can be changed).

In Snowflake, the day formats works as Day of the month (DD format in Oracle).

In order to get the same result as Oracle, function would need to use dayofweek or dayofweekiso formats, which are not currently supported by the Snowflake TRUNC function.

When this happens in a default clause of a column definition, the format is changed to DD and EWI 3117 is applied. But, when the function is used within a query, The TRUNC is replaced by a TRUNC_UDF helper which will replicate Oracle's behavior and the functionality will be completely equivalent.

Example Code

Input Code:

-- To TRUNC UDF with DAY format 
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'DAY') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'DY') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'D') FROM DUAL;

CREATE TABLE t1
(
    column1 DATE DEFAULT TRUNC(CURRENT_DATE, 'DAY'),
    column1 DATE DEFAULT TRUNC(CURRENT_DATE, 'DY'),
    column1 DATE DEFAULT TRUNC(CURRENT_DATE, 'D')
);

Output Code:

-- To TRUNC UDF with DAY format 
SELECT
    TRUNC_UDF(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'DAY') /*** MSC-WARNING - MSCEWI3117 - DAY FORMAT WORKS DIFFERENTLY BETWEEN ORACLE AND SNOWFLAKE. CUSTOM UDF 'TRUNC_UDF' INSERTED ***/ FROM DUAL UNION ALL
SELECT
    TRUNC_UDF(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'DY') /*** MSC-WARNING - MSCEWI3117 - DAY FORMAT WORKS DIFFERENTLY BETWEEN ORACLE AND SNOWFLAKE. CUSTOM UDF 'TRUNC_UDF' INSERTED ***/ FROM DUAL UNION ALL
SELECT
    TRUNC_UDF(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'D') /*** MSC-WARNING - MSCEWI3117 - DAY FORMAT WORKS DIFFERENTLY BETWEEN ORACLE AND SNOWFLAKE. CUSTOM UDF 'TRUNC_UDF' INSERTED ***/ FROM DUAL;

                                                                                                                                                                                                                    /*** MSC-WARNING - MSCEWI1039 - SEMANTIC INFORMATION COULD NOT BE LOADED FOR t1. CHECK IF THE NAME IS INVALID OR DUPLICATED. ***/
CREATE OR REPLACE TABLE PUBLIC.t1 (
    column1 DATE DEFAULT TRUNC(CURRENT_DATE, 'DD') /*** MSC-WARNING - MSCEWI3117 - DAY FORMAT WORKS DIFFERENTLY BETWEEN ORACLE AND SNOWFLAKE. FORMAT WAS REPLACED WITH 'DD' ***/,
    column1 DATE DEFAULT TRUNC(CURRENT_DATE, 'DD') /*** MSC-WARNING - MSCEWI3117 - DAY FORMAT WORKS DIFFERENTLY BETWEEN ORACLE AND SNOWFLAKE. FORMAT WAS REPLACED WITH 'DD' ***/,
    column1 DATE DEFAULT TRUNC(CURRENT_DATE, 'DD') /*** MSC-WARNING - MSCEWI3117 - DAY FORMAT WORKS DIFFERENTLY BETWEEN ORACLE AND SNOWFLAKE. FORMAT WAS REPLACED WITH 'DD' ***/
);

Recommendations

• For column definitions, you can replace the TRUNC function with:

TRUNC(date,'week') - N

Where N has to be changed manually based on the configuration of the first day of the week used in Oracle.

• If you need more support, you can email us at [email protected]