DATEDIFF UDF

Description

This UDF is used as a template for all cases when there is a subtraction between a DATE, TIMESTAMP, and any other type (except Intervals).

Custom UDF overloads

DATE_DIFF_UDF(date, date)

Parameters

1. FIRST_PARAM: The first DATE of the operation.

2. SECOND_PARAM: The DATE to be subtracted.

UDF

CREATE OR REPLACE FUNCTION PUBLIC.DATE_DIFF_UDF(FIRST_PARAM DATE, SECOND_PARAM DATE)
RETURNS INTEGER
LANGUAGE SQL
IMMUTABLE
AS
$$
	FIRST_PARAM - SECOND_PARAM
$$;

DATE_DIFF_UDF(date, timestamp)

Parameters

1. FIRST_PARAM: The first DATE of the operation.

2. SECOND_PARAM: The TIMESTAMP to be subtracted.

UDF

CREATE OR REPLACE FUNCTION PUBLIC.DATE_DIFF_UDF(FIRST_PARAM DATE, SECOND_PARAM TIMESTAMP)
RETURNS INTEGER
LANGUAGE SQL
IMMUTABLE
AS
$$
	FIRST_PARAM - SECOND_PARAM::DATE
$$;

DATE_DIFF_UDF(date, integer)

Parameters

1. FIRST_PARAM: The first DATE of the operation.

2. SECOND_PARAM: The INTEGER to be subtracted.

UDF

CREATE OR REPLACE FUNCTION PUBLIC.DATE_DIFF_UDF(FIRST_PARAM DATE, SECOND_PARAM INTEGER)
RETURNS DATE
LANGUAGE SQL
IMMUTABLE
AS
$$
	DATEADD(day,SECOND_PARAM*-1 ,FIRST_PARAM)
$$;

DATE_DIFF_UDF(timestamp, timestamp)

Parameters

1. FIRST_PARAM: The first TIMESTAMP of the operation.

2. SECOND_PARAM: The TIMESTAMP to be subtracted.

UDF

CREATE OR REPLACE FUNCTION PUBLIC.DATE_DIFF_UDF(FIRST_PARAM TIMESTAMP, SECOND_PARAM TIMESTAMP)
RETURNS INTEGER
LANGUAGE SQL
IMMUTABLE
AS
$$
	DATEDIFF(day,SECOND_PARAM ,FIRST_PARAM)
$$;

DATE_DIFF_UDF(timestamp, date)

Parameters

1. FIRST_PARAM: The first TIMESTAMP of the operation.

2. SECOND_PARAM: The DATE to be subtracted.

UDF

CREATE OR REPLACE FUNCTION PUBLIC.DATE_DIFF_UDF(FIRST_PARAM TIMESTAMP, SECOND_PARAM DATE)
RETURNS INTEGER
LANGUAGE SQL
IMMUTABLE
AS
$$
	DATEDIFF(day,SECOND_PARAM ,FIRST_PARAM)
$$;

DATE_DIFF_UDF(timestamp, number)

Parameters

1. FIRST_PARAM: The first TIMESTAMP of the operation.

2. SECOND_PARAM: The NUMBER to be subtracted.

UDF

CREATE OR REPLACE FUNCTION PUBLIC.DATE_DIFF_UDF(FIRST_PARAM TIMESTAMP, SECOND_PARAM NUMBER)
RETURNS TIMESTAMP
LANGUAGE SQL
IMMUTABLE
AS
$$
	DATEADD(day,SECOND_PARAM*-1,FIRST_PARAM)
$$;

Usage example

The unknown is a column whose type could not be resolved, it could be a timestamp, date integer, or number.

Oracle

Query

--Create Table
CREATE TABLE times(AsTimeStamp TIMESTAMP, AsDate DATE);

--Subtraction operations
SELECT AsDate - unknown FROM times, unknown_table;
SELECT unknown - AsTimeStamp FROM times;
SELECT AsTimeStamp - unknown FROM times;
SELECT unknown - AsDate FROM times;

Snowflake

Query

--Create Table
CREATE OR REPLACE TABLE times (AsTimeStamp TIMESTAMP(6),
AsDate TIMESTAMP /*** MSC-WARNING - MSCEWI3060 - DEFAULT VALUE FOR SYSDATE IS CURRENT_TIMESTAMP. COLUMN WAS TRANSFORMED TO TIMESTAMP TO PRESERVE INFORMATION. ***/
);

--Subtraction operations
--** MSC-WARNING - MSCEWI1050 - MISSING DEPENDENT OBJECT "unknown_table" **
SELECT
--** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'DATE_DIFF_UDF' INSERTED. **
PUBLIC.DATE_DIFF_UDF(
                      --** MSC-WARNING - MSCEWI3061 - COLUMN WAS TRANSFORMED FROM DATE TO TIMESTAMP.  SOME OPERATIONS MAY BE AFFECTED **
                      --** MSC-WARNING - MSCEWI3036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '-' MAY NOT BEHAVE CORRECTLY BETWEEN DATE AND unknown **
                      AsDate, unknown) FROM
times,
unknown_table;

SELECT
--** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'DATE_DIFF_UDF' INSERTED. **
PUBLIC.DATE_DIFF_UDF(
                      --** MSC-WARNING - MSCEWI3036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '-' MAY NOT BEHAVE CORRECTLY BETWEEN unknown AND Timestamp **
                      unknown, AsTimeStamp) FROM
times;

SELECT
--** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'DATE_DIFF_UDF' INSERTED. **
PUBLIC.DATE_DIFF_UDF(
                      --** MSC-WARNING - MSCEWI3036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '-' MAY NOT BEHAVE CORRECTLY BETWEEN Timestamp AND unknown **
                      AsTimeStamp, unknown) FROM
times;

SELECT
--** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'DATE_DIFF_UDF' INSERTED. **
PUBLIC.DATE_DIFF_UDF(
                      --** MSC-WARNING - MSCEWI3036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '-' MAY NOT BEHAVE CORRECTLY BETWEEN unknown AND DATE **
                      unknown,
                               --** MSC-WARNING - MSCEWI3061 - COLUMN WAS TRANSFORMED FROM DATE TO TIMESTAMP.  SOME OPERATIONS MAY BE AFFECTED **
                               AsDate) FROM
times;

Known Issues

1. Functional differences for timestamps

Sometimes the Snowflake value returned by the UDF may differ from the Oracle one due to the time. Consider the following example

Oracle

Query

-- CREATE TABLE UNKNOWN_TABLE(Unknown timestamp);
-- INSERT  INTO UNKNOWN_TABLE VALUES (TO_TIMESTAMP('01/10/09, 12:00 P.M.', 'dd/mm/yy, hh:mi P.M.'));

CREATE TABLE TIMES(AsTimeStamp TIMESTAMP);
INSERT INTO TIMES VALUES (TO_TIMESTAMP('05/11/21, 11:00 A.M.', 'dd/mm/yy, hh:mi A.M.'));

SELECT AsTimeStamp - unknown FROM times, unknown_table;v

Result

|ASTIMESTAMP-UNKNOWN|
|-------------------|
|4417 23:0:0.0      |

Snowflake

Query

-- CREATE TABLE UNKNOWN_TABLE(Unknown timestamp);
-- INSERT  INTO UNKNOWN_TABLE VALUES (TO_TIMESTAMP('01/10/09, 12:00 P.M.', 'dd/mm/yy, hh:mi P.M.'));
CREATE OR REPLACE TABLE TIMES (AsTimeStamp TIMESTAMP(6)
);

INSERT INTO TIMES
VALUES (TO_TIMESTAMP('05/11/21, 11:00 A.M.', 'dd/mm/yy, hh:mi A.M.'));

--** MSC-WARNING - MSCEWI1050 - MISSING DEPENDENT OBJECT "unknown_table" **

SELECT
--** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'DATE_DIFF_UDF' INSERTED. **
PUBLIC.DATE_DIFF_UDF(
                      --** MSC-WARNING - MSCEWI3036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '-' MAY NOT BEHAVE CORRECTLY BETWEEN Timestamp AND unknown **
                      AsTimeStamp, unknown) FROM
times,
unknown_table;
-- ** MSC-ERROR - MSCEWI1001 - UNRECOGNIZED TOKEN ON LINE 7 COLUMN 55 OF THE SOURCE CODE STARTING AT 'v'. EXPECTED 'STATEMENT' GRAMMAR. **
--              v

Result

PUBLIC.DATEDIFF_UDF( ASTIMESTAMP, UNKNOWN)|
------------------------------------------+
                                      4418|

Related EWIs

1. MSCEWI1020: CUSTOM UDF INSERTED.