TRUNC (date) UDF

Description

The TRUNC (date) function returns date with the time portion of the day truncated to the unit specified by the format model fmt. (Oracle TRUNC(date) SQL Language Reference)

TRUNC(date [, fmt ])

Oracle TRUNC and Snowflake TRUNC function with date arguments have some functional differences.

TRUNC_UDF helper will be added to handle the following cases:

1. The format is not supported by Snowflake.

2. The format exists in Snowflake but works differently.

3. The tool cannot determine the datatype of the first argument.

4. The format is provided as a column or expression and not as a literal.

Please refer to EWI3083 and EWI3117 for examples of each case.

Custom UDF overloads

TRUNC_UDF(date)

It applies an explicit DATE cast to the input Timestamp.

Parameters

1. INPUT: The Timestamp with Time Zone (TIMESTAMP_LTZ) that needs to be truncated.

The default parameter for the UDF is TIMESTAMP_LTZ. It may need to be changed to TIMESTAMP_TZ or TIMESTAMP_NTZ to match the default TIMESTAMP used by the user.

UDF

CREATE OR REPLACE FUNCTION PUBLIC.TRUNC_UDF(INPUT TIMESTAMP_LTZ)
RETURNS DATE
IMMUTABLE
AS
$$
    INPUT::DATE
$$;

Oracle

Query

SELECT
TRUNC(
	TO_TIMESTAMP ( '20-Mar-1996 21:01:11 ', 'DD-Mon-YYYY HH24:MI:SS' )
	)
"Date" FROM DUAL;

Result

Snowflake

Query

SELECT

TRUNC_UDF(
TO_TIMESTAMP ( '20-Mar-1996 21:01:11 ', 'DD-Mon-YYYY HH24:MI:SS' )
) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'TRUNC_UDF' INSERTED. ***/ Date
FROM DUAL;

Result

TRUNC_UDF(date, fmt)

Manually creates a new date using DATE_FROM_PARTS() function, depending on the format category used.

Parameters

1. DATE_TO_TRUNC: The Timestamp with Time Zone (TIMESTAMP_LTZ) that needs to be truncated.

2. DATE_FMT: The date format as a VARCHAR. Same formats that are supported in Oracle.

The default parameter for the UDF is TIMESTAMP_LTZ. It may need to be changed to TIMESTAMP_TZ or TIMESTAMP_NTZ to match the default TIMESTAMP used by the user.

UDF

CREATE OR REPLACE FUNCTION PUBLIC.TRUNC_UDF(DATE_TO_TRUNC TIMESTAMP_LTZ, DATE_FMT VARCHAR(5))
RETURNS DATE
AS
$$
CAST(CASE 
WHEN UPPER(DATE_FMT) IN ('CC','SCC') THEN DATE_FROM_PARTS(CAST(LEFT(CAST(YEAR(DATE_TO_TRUNC) as CHAR(4)),2) || '01' as INTEGER),1,1)
WHEN UPPER(DATE_FMT) IN ('SYYYY','YYYY','YEAR','SYEAR','YYY','YY','Y') THEN DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),1,1)
WHEN UPPER(DATE_FMT) IN ('IYYY','IYY','IY','I') THEN 
    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        
WHEN UPPER(DATE_FMT) IN ('MONTH','MON','MM','RM') THEN DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),MONTH(DATE_TO_TRUNC),1)
WHEN UPPER(DATE_FMT)IN ('Q') THEN DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),(QUARTER(DATE_TO_TRUNC)-1)*3+1,1)
WHEN UPPER(DATE_FMT) IN ('WW') THEN DATEADD(DAY, 0-MOD(TIMESTAMPDIFF(DAY,DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),1,1),DATE_TO_TRUNC),7), DATE_TO_TRUNC)
WHEN UPPER(DATE_FMT) IN ('IW') THEN 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)
WHEN UPPER(DATE_FMT) IN ('W') THEN 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)                                                             
WHEN UPPER(DATE_FMT) IN ('DDD', 'DD','J') THEN DATE_TO_TRUNC
WHEN UPPER(DATE_FMT) IN ('DAY', 'DY','D') THEN DATEADD(DAY, 0-DAYOFWEEK(DATE_TO_TRUNC), DATE_TO_TRUNC)
WHEN UPPER(DATE_FMT) IN ('HH', 'HH12','HH24') THEN DATE_TO_TRUNC
WHEN UPPER(DATE_FMT) IN ('MI') THEN DATE_TO_TRUNC
END AS DATE)
$$
;

TRUNC format scenarios

The results format depends on the DateTime output formats configurated for the database.

1. Natively supported formats

Oracle

Query

SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'YYYY') FROM DUAL UNION ALL 
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'YEAR') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'YYY') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'YY') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'Y') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'Q') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'MONTH') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'MON') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'MM') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'DD') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'HH') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'MI') FROM DUAL;

Result

Snowflake

Query

SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'YYYY') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'YEAR') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'YYY') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'YY') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'Y') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'Q') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'MONTH') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'MON') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'MM') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'DD') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'HH') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'MI') FROM DUAL;

Result

2. Formats mapped to another format

Oracle

Query

SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS')) FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'SYYYY') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'SYEAR') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'RM') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'IW') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'DDD') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'J') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'HH12') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'HH24') FROM DUAL;

Result

Snowflake

Query

SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'), 'DD') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'), 'YYYY') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'), 'YEAR') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'), 'MM') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'), 'WK') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'), 'DD') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'), 'D') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'), 'HH') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'), 'HH') FROM DUAL;

Result

3. Day formats

Please check MSCEWI3117 documentation

Oracle

Query

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;

Result

Snowflake

Query

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;

Result

4. Unsupported formats

Please check MSCEWI3083 documentation

Oracle

Query

SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'CC') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'SCC') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'IYYY') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'IY') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'I') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'WW') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'W') FROM DUAL;

Result

Snowflake

Query

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 UNION ALL
SELECT
    TRUNC_UDF(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'SCC') /*** MSC-WARNING - MSCEWI3083 - UNSUPPORTED FORMAT 'SCC' FOR TRUNC FUNCTION. 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'),'IYYY') /*** MSC-WARNING - MSCEWI3083 - UNSUPPORTED FORMAT 'IYYY' FOR TRUNC FUNCTION. 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'),'IY') /*** MSC-WARNING - MSCEWI3083 - UNSUPPORTED FORMAT 'IY' FOR TRUNC FUNCTION. 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'),'I') /*** MSC-WARNING - MSCEWI3083 - UNSUPPORTED FORMAT 'I' FOR TRUNC FUNCTION. 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'),'WW') /*** MSC-WARNING - MSCEWI3083 - UNSUPPORTED FORMAT 'WW' FOR TRUNC FUNCTION. 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'),'W') /*** MSC-WARNING - MSCEWI3083 - UNSUPPORTED FORMAT 'W' FOR TRUNC FUNCTION. CUSTOM UDF 'TRUNC_UDF' INSERTED ***/ FROM DUAL;

Result

Known Issues

1. Oracle DATE contains TIMESTAMP

Take into consideration that Oracle DATE contains an empty TIMESTAMP (00:00:00.000), while Snowflake DATE does not. SnowConvert allows transforming DATE to TIMESTAMP with the SysdateAsCurrentTimestamp flag.

Related EWIs

1. MSCEWI3083: Unhandled arguments for the TRUNC function.

2. MSCEWI3117: Day format works differently between Oracle and Snowflake.