DATEDIFF UDF

Some parts in the output code are omitted for clarity reasons.

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

DATEDIFF_UDF(date, date)

Parameters

  1. FIRST_PARAM: The first DATE of the operation.

  2. SECOND_PARAM: The DATE to be subtracted.

CREATE OR REPLACE FUNCTION PUBLIC.DATEDIFF_UDF(FIRST_PARAM DATE, SECOND_PARAM DATE)
RETURNS INTEGER
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
	FIRST_PARAM - SECOND_PARAM
$$;

DATEDIFF_UDF(date, timestamp)

Parameters

  1. FIRST_PARAM: The first DATE of the operation.

  2. SECOND_PARAM: The TIMESTAMP to be subtracted.

CREATE OR REPLACE FUNCTION PUBLIC.DATEDIFF_UDF(FIRST_PARAM DATE, SECOND_PARAM TIMESTAMP)
RETURNS INTEGER
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
	FIRST_PARAM - SECOND_PARAM::DATE
$$;

DATEDIFF_UDF(date, integer)

Parameters

  1. FIRST_PARAM: The first DATE of the operation.

  2. SECOND_PARAM: The INTEGER to be subtracted.

CREATE OR REPLACE FUNCTION PUBLIC.DATEDIFF_UDF(FIRST_PARAM DATE, SECOND_PARAM INTEGER)
RETURNS DATE
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
	DATEADD(day,SECOND_PARAM*-1 ,FIRST_PARAM)
$$;

DATEDIFF_UDF(timestamp, timestamp)

Parameters

  1. FIRST_PARAM: The first TIMESTAMP of the operation.

  2. SECOND_PARAM: The TIMESTAMP to be subtracted.

CREATE OR REPLACE FUNCTION PUBLIC.DATEDIFF_UDF(FIRST_PARAM TIMESTAMP, SECOND_PARAM TIMESTAMP)
RETURNS INTEGER
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
	DATEDIFF(day,SECOND_PARAM ,FIRST_PARAM)
$$;

DATEDIFF_UDF(timestamp, date)

Parameters

  1. FIRST_PARAM: The first TIMESTAMP of the operation.

  2. SECOND_PARAM: The DATE to be subtracted.

CREATE OR REPLACE FUNCTION PUBLIC.DATEDIFF_UDF(FIRST_PARAM TIMESTAMP, SECOND_PARAM DATE)
RETURNS INTEGER
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
	DATEDIFF(day,SECOND_PARAM ,FIRST_PARAM)
$$;

DATEDIFF_UDF(timestamp, number)

Parameters

  1. FIRST_PARAM: The first TIMESTAMP of the operation.

  2. SECOND_PARAM: The NUMBER to be subtracted.

CREATE OR REPLACE FUNCTION PUBLIC.DATEDIFF_UDF(FIRST_PARAM TIMESTAMP, SECOND_PARAM NUMBER)
RETURNS TIMESTAMP
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
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.

--disableDateAsTimestamp

Flag to indicate whether SYSDATE should be transformed into CURRENT_DATE or CURRENT_TIMESTAMP. This will also affect all DATE columns that will be transformed to TIMESTAMP.

Oracle

IN -> Oracle_01.sql
--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

OUT -> Oracle_01.sql
--Create Table
CREATE OR REPLACE TABLE times (AsTimeStamp TIMESTAMP(6),
AsDate TIMESTAMP
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

--Subtraction operations
SELECT
PUBLIC.DATEDIFF_UDF(
    !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '-' MAY NOT BEHAVE CORRECTLY BETWEEN DATE AND unknown ***/!!!
    AsDate,
    unknown
)
FROM
times,
unknown_table;

SELECT
PUBLIC.DATEDIFF_UDF(
    !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '-' MAY NOT BEHAVE CORRECTLY BETWEEN unknown AND Timestamp ***/!!!
    unknown,
    AsTimeStamp
)
FROM
times;

SELECT
PUBLIC.DATEDIFF_UDF(
    !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '-' MAY NOT BEHAVE CORRECTLY BETWEEN Timestamp AND unknown ***/!!!
    AsTimeStamp,
    unknown
)
FROM
times;

SELECT
PUBLIC.DATEDIFF_UDF(
    !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '-' MAY NOT BEHAVE CORRECTLY BETWEEN unknown AND DATE ***/!!!
    unknown,
    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

IN -> Oracle_02.sql
-- 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;

Snowflake

OUT -> Oracle_02.sql
-- 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)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

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

SELECT
PUBLIC.DATEDIFF_UDF(
    !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '-' MAY NOT BEHAVE CORRECTLY BETWEEN Timestamp AND unknown ***/!!!
    AsTimeStamp,
    unknown
)
FROM
times,
unknown_table;

Related EWIs

  1. SSC-EWI-OR0036: Types resolution issues, the arithmetic operation may not behave correctly between string and date.

PreviousDATEADD UDFNextINTERVAL UDFs

Last updated