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.

Custom UDF overloads

TRUNC_UDF(date)

It applies an explicit DATE cast to the input Timestamp.

Parameters

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.

CREATE OR REPLACE FUNCTION PUBLIC.TRUNC_UDF(INPUT TIMESTAMP_LTZ)
RETURNS DATE
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
    INPUT::DATE
$$;

Oracle

IN -> Oracle_01.sql

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

Date                   |
-----------------------+
1996-03-20 00:00:00.000|

Snowflake

OUT -> Oracle_01.sql

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

DATE      |
----------+
1996-03-20|

TRUNC_UDF(date, fmt)

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

Parameters

DATE_TO_TRUNC: The Timestamp with Time Zone (TIMESTAMP_LTZ) that needs to be truncated.
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.

CREATE OR REPLACE FUNCTION PUBLIC.TRUNC_UDF(DATE_TO_TRUNC TIMESTAMP_LTZ, DATE_FMT VARCHAR(5))
RETURNS DATE
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
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

IN -> Oracle_02.sql

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;

+───────────────────────────────────────────────────────────────────────+
| "TRUNC(TO_DATE('20/04/202213:21:10','DD/MM/YYYYHH24:MI:SS'),'YYYY')"  |
+───────────────────────────────────────────────────────────────────────+
| 01-JAN-22                                                             |
| 01-JAN-22                                                             |
| 01-JAN-22                                                             |
| 01-JAN-22                                                             |
| 01-JAN-22                                                             |
| 01-APR-22                                                             |
| 01-APR-22                                                             |
| 01-APR-22                                                             |
| 01-APR-22                                                             |
| 20-APR-22                                                             |
| 20-APR-22                                                             |
| 20-APR-22                                                             |
+───────────────────────────────────────────────────────────────────────+

Snowflake

OUT -> Oracle_02.sql

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;

+─────────────────────────────────────────────────────────────────────────+
| "TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'YYYY')"  |
+─────────────────────────────────────────────────────────────────────────+
| 2022-01-01                                                              |
| 2022-01-01                                                              |
| 2022-01-01                                                              |
| 2022-01-01                                                              |
| 2022-01-01                                                              |
| 2022-04-01                                                              |
| 2022-04-01                                                              |
| 2022-04-01                                                              |
| 2022-04-01                                                              |
| 2022-04-20                                                              |
| 2022-04-20                                                              |
| 2022-04-20                                                              |
+─────────────────────────────────────────────────────────────────────────+

2. Formats mapped to another format

Oracle

IN -> Oracle_03.sql

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;

+────────────────────────────────────────────────────────────────+
| "TRUNC(TO_DATE('20/04/202213:21:10','DD/MM/YYYYHH24:MI:SS'))"  |
+────────────────────────────────────────────────────────────────+
| 20-APR-22                                                      |
| 01-JAN-22                                                      |
| 01-JAN-22                                                      |
| 01-APR-22                                                      |
| 18-APR-22                                                      |
| 20-APR-22                                                      |
| 20-APR-22                                                      |
| 20-APR-22                                                      |
| 20-APR-22                                                      |
+────────────────────────────────────────────────────────────────+

Snowflake

OUT -> Oracle_03.sql

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;

+────────────────────────────────────────────────────────────────────────+
| "TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'), 'DD')"  |
+────────────────────────────────────────────────────────────────────────+
| 2022-04-20                                                             |
| 2022-01-01                                                             |
| 2022-01-01                                                             |
| 2022-04-01                                                             |
| 2022-04-18                                                             |
| 2022-04-20                                                             |
| 2022-04-20                                                             |
| 2022-04-20                                                             |
| 2022-04-20                                                             |
+────────────────────────────────────────────────────────────────────────+

3. Day formats

Please check MSCEWI3117 documentation

Oracle

IN -> Oracle_04.sql

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;

+──────────────────────────────────────────────────────────────────────+
| "TRUNC(TO_DATE('20/04/202213:21:10','DD/MM/YYYYHH24:MI:SS'),'DAY')"  |
+──────────────────────────────────────────────────────────────────────+
| 17-APR-22                                                            |
| 17-APR-22                                                            |
| 17-APR-22                                                            |
+──────────────────────────────────────────────────────────────────────+

Snowflake

OUT -> Oracle_04.sql

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

+────────────────────────────────────────────────────────────────────────────+
| "TRUNC_UDF(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'DAY')"  |
+────────────────────────────────────────────────────────────────────────────+
| 2022-04-17                                                                 |
| 2022-04-17                                                                 |
| 2022-04-17                                                                 |
+────────────────────────────────────────────────────────────────────────────+

4. Unsupported formats

Oracle

IN -> Oracle_05.sql

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;

+─────────────────────────────────────────────────────────────────────+
| "TRUNC(TO_DATE('20/04/202213:21:10','DD/MM/YYYYHH24:MI:SS'),'CC')"  |
+─────────────────────────────────────────────────────────────────────+
| 01-JAN-01                                                           |
| 01-JAN-01                                                           |
| 03-JAN-22                                                           |
| 03-JAN-22                                                           |
| 03-JAN-22                                                           |
| 16-APR-22                                                           |
| 15-APR-22                                                           |
+─────────────────────────────────────────────────────────────────────+

Snowflake

OUT -> Oracle_05.sql

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

+───────────────────────────────────────────────────────────────────────────+
| "TRUNC_UDF(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'CC')"  |
+───────────────────────────────────────────────────────────────────────────+
| 2001-01-01                                                                |
| 2001-01-01                                                                |
| 2022-01-03                                                                |
| 2022-01-03                                                                |
| 2022-01-03                                                                |
| 2022-04-16                                                                |
| 2022-04-15                                                                |
+───────────────────────────────────────────────────────────────────────────+

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.

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 flag.

No related EWIs.

PreviousTIMESTAMP DIFF UDF NextTRUNC (number) UDF

Last updated 6 months ago

Description

Custom UDF overloads

TRUNC_UDF(date)

Oracle

Snowflake

TRUNC_UDF(date, fmt)

TRUNC format scenarios

1. Natively supported formats

Oracle

Snowflake

2. Formats mapped to another format

Oracle

Snowflake

3. Day formats

Oracle

Snowflake

4. Unsupported formats

Oracle

Snowflake

Known Issues

1. Oracle DATE contains TIMESTAMP

Related EWIs