MSCEWI3117
Day format works differently between Oracle and Snowflake.
Low
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.-- 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')
);
-- 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' ***/
);
- 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.
Last modified 3mo ago