MSCEWI3083

Unhandled arguments for the TRUNC function

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

Severity

Low

Description

This warning is added when the TRUNC function is used with an unsupported format or a parameter that cannot be handled by SnowConvert. To avoid any issues, the format is replaced with a valid format, or TRUNC_UDF is added.

The behavior will vary if the TRUNC is being used in a query or if it is being used in a Default clause of a column definition.

Example Code

Input Code:

-- EWI 3083 added in queries
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'CC') FROM DUAL;
SELECT TRUNC(undefinedColumn) FROM table1;
SELECT TRUNC(CURRENT_DATE, formatAsColumn) FROM table1;

-- EWI 3083 added in default column definition
CREATE TABLE table2
(
    COL1 DATE DEFAULT TRUNC(CURRENT_DATE, 'CC'),
    COL2 DATE DEFAULT TRUNC(CURRENT_DATE, 'M' || 'M')
);

Output Code:

-- EWI 3083 added in queries
SELECT
    TRUNC_UDF(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'CC') /*** MSC-WARNING - MSCEWI3083 - UNSUPPORTED FORMAT 'CC' FOR TRUNC FUNCTION. CUSTOM UDF 'TRUNC_UDF' INSERTED ***/ FROM DUAL;

SELECT
    TRUNC_UDF(undefinedColumn) /*** MSC-WARNING - MSCEWI3083 - UNKNOWN ARGUMENT FOR TRUNC FUNCTION. CUSTOM UDF 'TRUNC_UDF' INSERTED ***/ FROM
    PUBLIC.table1;

SELECT
    TRUNC_UDF(CURRENT_DATE, formatAsColumn) /*** MSC-WARNING - MSCEWI3083 - UNKNOWN FORMAT FOR TRUNC FUNCTION. CUSTOM UDF 'TRUNC_UDF' INSERTED ***/ FROM
    PUBLIC.table1;

-- EWI 3083 added in default column definition
CREATE OR REPLACE TABLE PUBLIC.table2 (
    COL1 DATE DEFAULT TRUNC(CURRENT_DATE, 'DD') /*** MSC-WARNING - MSCEWI3083 - UNSUPPORTED FORMAT 'CC' FOR TRUNC FUNCTION. FORMAT WAS REPLACED WITH 'DD' ***/,
    COL2 DATE DEFAULT TRUNC(CURRENT_DATE, 'DD') /*** MSC-WARNING - MSCEWI3083 - UNKNOWN FORMAT FOR TRUNC FUNCTION. FORMAT WAS REPLACED WITH 'DD' ***/
);

TRUNC_UDF is capable of handling any case, whether the parameter is a number or a date. Also, it handles any Oracle format. However, User-defined functions cannot be called from a default clause in a column definition. For these cases, the format is changed to 'DD' which means Day of month.

For all the cases where the format is supported in Snowflake, the TRUNC function is kept as is.

Alternatives

In the case of column definitions, depending on the format, you can replace the TRUNC function with other expressions to get the same results:

Format(s)	Alternate expression
CC, SCC	DATE_FROM_PARTS(CAST(LEFT(CAST(YEAR(DATE_TO_TRUNC) as CHAR(4)),2) || '01' as INTEGER),1,1)
IYYY, IYY, IY, I	CASE DAYOFWEEK(DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),1,1)) WHEN 0 THEN DATEADD(DAY, 1, DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),1,1)) WHEN 1 THEN DATEADD(DAY, 0, DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),1,1)) WHEN 2 THEN DATEADD(DAY, -1, DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),1,1)) WHEN 3 THEN DATEADD(DAY, -2, DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),1,1)) WHEN 4 THEN DATEADD(DAY, -3, DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),1,1)) WHEN 5 THEN DATEADD(DAY, 3, DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),1,1)) WHEN 6 THEN DATEADD(DAY, 2, DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),1,1)) END
WW	DATEADD(DAY, 0-MOD(TIMESTAMPDIFF(DAY,DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),1,1),DATE_TO_TRUNC),7), DATE_TO_TRUNC)
IW	DATEADD(DAY, 0-MOD(TIMESTAMPDIFF(DAY,(CASE DAYOFWEEK(DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),1,1)) WHEN 0 THEN DATEADD(DAY, 1, DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),1,1)) WHEN 1 THEN DATEADD(DAY, 0, DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),1,1)) WHEN 2 THEN DATEADD(DAY, -1, DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),1,1)) WHEN 3 THEN DATEADD(DAY, -2, DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),1,1)) WHEN 4 THEN DATEADD(DAY, -3, DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),1,1)) WHEN 5 THEN DATEADD(DAY, 3, DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),1,1)) WHEN 6 THEN DATEADD(DAY, 2, DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),1,1)) END), DATE_TO_TRUNC),7), DATE_TO_TRUNC)
W	DATEADD(DAY, 0-MOD(TIMESTAMPDIFF(DAY,DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),MONTH(DATE_TO_TRUNC),1),DATE_TO_TRUNC),7), DATE_TO_TRUNC)

Replace DATE_TO_TRUNC with the first argument of the TRUNC function

The 'W' format is marked as unsupported because its behavior is different in Snowflake and Oracle

Recommendations

• Replace the TRUNC call with the corresponding example above.

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