DATEDIFF UDF INTERVAL

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

Description

This UDF is used to resolve operations with intervals like:

INTERVAL - UNKNOWN
UNKNOWN - INTERVAL
DATE - INTERVAL
TIMESTAMP - INTERVAL

An UNKNOWN type is a column or expression whose type could not be resolved by Snow Convert, it use to happen when the DDLs for tables are not included in the migration or when there is an expression or subquery that can return different data types.

Custom UDF overloads

DATEADD_DDIF(string, date)

Parameters

INTERVAL_VALUE: The interval String of the operation.
D: The DATE where the interval will be subtracted.

CREATE OR REPLACE FUNCTION PUBLIC.DATEDIFF_UDF(INTERVAL_VALUE STRING,D DATE)
RETURNS DATE
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
WITH VARS(INPUT_VALUE, INPUT_PART) AS (
SELECT SUBSTR(INTERVAL_VALUE,11,POSITION('''',INTERVAL_VALUE,11)-11),
       TRIM(SUBSTR(INTERVAL_VALUE,POSITION('''',INTERVAL_VALUE,11)+1)))
SELECT 
    CASE WHEN INPUT_PART='YEAR(2) TO MONTH' OR INPUT_PART='YEAR(4) TO MONTH' THEN
        DATEADD(MONTHS,-1*PUBLIC.INTERVAL_TO_MONTHS_UDF(INPUT_VALUE),D) 
    WHEN INPUT_PART='MONTH' THEN
        DATEADD(MONTHS,-1*TO_NUMBER(INPUT_VALUE),D)    
    ELSE
        DATEADD(MICROSECONDS,-1*1000000*PUBLIC.INTERVAL_TO_SECONDS_UDF(INPUT_PART, INPUT_VALUE),D)::DATE
    END CASE    
FROM VARS    
$$;

DATEADD_DIFF(date, string)

Parameters

D: The DATE where the interval will be subtracted.
INTERVAL_VALUE: The interval String of the operation.

CREATE OR REPLACE FUNCTION PUBLIC.DATEDIFF_UDF(D DATE, INTERVAL_VALUE STRING)
RETURNS DATE
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
WITH VARS(INPUT_VALUE, INPUT_PART) AS (
SELECT SUBSTR(INTERVAL_VALUE,11,POSITION('''',INTERVAL_VALUE,11)-11),
       TRIM(SUBSTR(INTERVAL_VALUE,POSITION('''',INTERVAL_VALUE,11)+1)))
SELECT 
    CASE WHEN INPUT_PART='YEAR(2) TO MONTH' OR INPUT_PART='YEAR(4) TO MONTH' THEN
        DATEADD(MONTHS,-1*PUBLIC.INTERVAL_TO_MONTHS_UDF(INPUT_VALUE),D) 
    WHEN INPUT_PART='MONTH' THEN
        DATEADD(MONTHS,-1*TO_NUMBER(INPUT_VALUE),D)    
    ELSE
        DATEADD(MICROSECONDS,-1*1000000*PUBLIC.INTERVAL_TO_SECONDS_UDF(INPUT_PART, INPUT_VALUE),D)::DATE
    END CASE    
FROM VARS    
$$;

DATEADD_DIFF(string, timestamp)

Parameters

INTERVAL_VALUE: The interval String of the operation.
D: The TIMESTAMP where the interval will be subtracted.

CREATE OR REPLACE FUNCTION PUBLIC.DATEDIFF_UDF(INTERVAL_VALUE STRING,D TIMESTAMP)
RETURNS TIMESTAMP
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
WITH VARS(INPUT_VALUE, INPUT_PART) AS (
SELECT SUBSTR(INTERVAL_VALUE,11,POSITION('''',INTERVAL_VALUE,11)-11),
       TRIM(SUBSTR(INTERVAL_VALUE,POSITION('''',INTERVAL_VALUE,11)+1)))
SELECT 
    CASE WHEN INPUT_PART='YEAR(2) TO MONTH' OR INPUT_PART='YEAR(4) TO MONTH' THEN
        DATEADD(MONTHS,-1*PUBLIC.INTERVAL_TO_MONTHS_UDF(INPUT_VALUE),D) 
    WHEN INPUT_PART='MONTH' THEN
        DATEADD(MONTHS,-1*TO_NUMBER(INPUT_VALUE),D)    
    ELSE
        DATEADD(MICROSECONDS,-1*1000000*PUBLIC.INTERVAL_TO_SECONDS_UDF(INPUT_PART, INPUT_VALUE),D)
    END CASE    
FROM VARS    
$$;

DATEADD_DIFF(timestamp, string)

Parameters

D: The TIMESTAMP where the interval will be subtracted.
INTERVAL_VALUE: The interval String of the operation.

CREATE OR REPLACE FUNCTION PUBLIC.DATEDIFF_UDF(D TIMESTAMP, INTERVAL_VALUE STRING)
RETURNS TIMESTAMP
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
WITH VARS(INPUT_VALUE, INPUT_PART) AS (
SELECT SUBSTR(INTERVAL_VALUE,11,POSITION('''',INTERVAL_VALUE,11)-11),
       TRIM(SUBSTR(INTERVAL_VALUE,POSITION('''',INTERVAL_VALUE,11)+1)))
SELECT 
    CASE WHEN INPUT_PART='YEAR(2) TO MONTH' OR INPUT_PART='YEAR(4) TO MONTH' THEN
        DATEADD(MONTHS,-1*PUBLIC.INTERVAL_TO_MONTHS_UDF(INPUT_VALUE),D) 
    WHEN INPUT_PART='MONTH' THEN
        DATEADD(MONTHS,-1*TO_NUMBER(INPUT_VALUE),D)    
    ELSE
        DATEADD(MICROSECONDS,-1*1000000*PUBLIC.INTERVAL_TO_SECONDS_UDF(INPUT_PART, INPUT_VALUE),D)
    END CASE    
FROM VARS    
$$;

Usage example

`--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

-- DROP TABLE UNKNOWN_TABLE;
-- 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,
AsTimestampTwo TIMESTAMP,
AsDate DATE,
AsDateTwo DATE
);

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

SELECT 
 AsTimeStamp-INTERVAL '1-1' YEAR(2) TO MONTH,
 AsTimeStamp-INTERVAL '2-1' YEAR(4) TO MONTH,
 AsTimeStamp-INTERVAL '1' MONTH,
 AsTimeStamp-INTERVAL '2' MONTH,
 AsDate-INTERVAL '1-1' YEAR(2) TO MONTH,
 AsDate-INTERVAL '2-1' YEAR(4) TO MONTH,
 AsDate-INTERVAL '1' MONTH,
 AsDate-INTERVAL '2' MONTH,
 Unknown-INTERVAL '1 01:00:00.222' DAY TO SECOND(3),
 Unknown-INTERVAL '1 01:10' DAY TO MINUTE,
 Unknown-INTERVAL '1 1' DAY TO HOUR
FROM TIMES, UNKNOWN_TABLE;

|ASTIMESTAMP-INTERVAL'1-1'YEAR(2)TOMONTH|ASTIMESTAMP-INTERVAL'2-1'YEAR(4)TOMONTH|ASTIMESTAMP-INTERVAL'1'MONTH|ASTIMESTAMP-INTERVAL'2'MONTH|ASDATE-INTERVAL'1-1'YEAR(2)TOMONTH|ASDATE-INTERVAL'2-1'YEAR(4)TOMONTH|ASDATE-INTERVAL'1'MONTH|ASDATE-INTERVAL'2'MONTH|UNKNOWN-INTERVAL'101:00:00.222'DAYTOSECOND(3)|UNKNOWN-INTERVAL'101:10'DAYTOMINUTE|UNKNOWN-INTERVAL'11'DAYTOHOUR|
|---------------------------------------|---------------------------------------|----------------------------|----------------------------|----------------------------------|----------------------------------|-----------------------|-----------------------|---------------------------------------------|-----------------------------------|-----------------------------|
|2020-10-05 11:00:00.000                |2019-10-05 11:00:00.000                |2021-10-05 11:00:00.000     |2021-09-05 11:00:00.000     |2020-10-06 00:00:00.000           |2019-10-06 00:00:00.000           |2021-10-06 00:00:00.000|2021-09-06 00:00:00.000|2009-09-30 10:59:59.778                      |2009-09-30 10:50:00.000            |2009-09-30 11:00:00.000      |

Snowflake

This configuration was used in Snowflake

ALTER SESSION SET TIMESTAMP_NTZ_OUTPUT_FORMAT= 'DD-MON-YY HH.MI.SS.FF6 AM';
ALTER SESSION SET DATE_OUTPUT_FORMAT= 'DD-MON-YY';

OUT -> Oracle_01.sql

-- DROP TABLE UNKNOWN_TABLE;
-- 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),
 AsTimestampTwo TIMESTAMP(6),
 AsDate DATE,
 AsDateTwo DATE
 )
 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.'),
TO_TIMESTAMP('05/11/21, 10:00 A.M.', 'dd/mm/yy, hh:mi A.M.'),
TO_DATE('06/11/21', 'dd/mm/yy'),
TO_DATE('05/11/21', 'dd/mm/yy'));


SELECT
 AsTimeStamp - INTERVAL '1y, 1mm',
 AsTimeStamp - INTERVAL '2y, 1mm',
 AsTimeStamp - INTERVAL '1 month',
 AsTimeStamp - INTERVAL '2 month',
 AsDate- INTERVAL '1y, 1mm',
 AsDate- INTERVAL '2y, 1mm',
 AsDate- INTERVAL '1 month',
 AsDate- INTERVAL '2 month',
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '-' MAY NOT BEHAVE CORRECTLY BETWEEN Unknown AND Interval ***/!!!
 Unknown - INTERVAL '1d, 01h, 00m, 00s, 222ms',
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '-' MAY NOT BEHAVE CORRECTLY BETWEEN Unknown AND Interval ***/!!!
 Unknown - INTERVAL '1d, 01h, 10m',
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '-' MAY NOT BEHAVE CORRECTLY BETWEEN Unknown AND Interval ***/!!!
 Unknown - INTERVAL '1d, 1h'
FROM
 TIMES,
 UNKNOWN_TABLE;

|DATEDIFF_UDF(ASTIMESTAMP,'INTERVAL ''1-1'' YEAR(2) TO MONTH')|DATEDIFF_UDF(ASTIMESTAMP,'INTERVAL ''2-1'' YEAR(4) TO MONTH')|DATEDIFF_UDF(ASTIMESTAMP,'INTERVAL ''1'' MONTH')|DATEDIFF_UDF(ASTIMESTAMP,'INTERVAL ''2'' MONTH')|DATEDIFF_UDF(ASDATE,'INTERVAL ''1-1'' YEAR(2) TO MONTH')|DATEDIFF_UDF(ASDATE,'INTERVAL ''2-1'' YEAR(4) TO MONTH')|DATEDIFF_UDF(ASDATE,'INTERVAL ''1'' MONTH')|DATEDIFF_UDF(ASDATE,'INTERVAL ''2'' MONTH')|DATEDIFF_UDF(UNKNOWN,'INTERVAL ''1 01:00:00.222'' DAY TO SECOND(3)')|DATEDIFF_UDF(UNKNOWN,'INTERVAL ''1 01:10'' DAY TO MINUTE')|DATEDIFF_UDF(UNKNOWN,'INTERVAL ''1 1'' DAY TO HOUR')|
|-------------------------------------------------------------|-------------------------------------------------------------|------------------------------------------------|------------------------------------------------|--------------------------------------------------------|--------------------------------------------------------|-------------------------------------------|-------------------------------------------|--------------------------------------------------------------------|----------------------------------------------------------|----------------------------------------------------|
|2020-10-05 11:00:00.000                                      |2019-10-05 11:00:00.000                                      |2021-10-05 11:00:00.000                         |2021-09-05 11:00:00.000                         |2020-10-06                                              |2019-10-06                                              |2021-10-06                                 |2021-09-06                                 |2009-09-30 10:59:59.778                                             |2009-09-30 10:50:00.000                                   |2009-09-30 11:00:00.000                             |

Known Issues

1. INTERVAL - INTERVAL Operation is not supported

Snowflake does not support INTERVAL - INTERVAL operations.

: Types resolution issues, the arithmetic operation may not behave correctly between string and date.

PreviousDATEADD UDF INTERVAL NextCAST_DATE UDF

Last updated 9 months ago

Description

Custom UDF overloads

DATEADD_DDIF(string, date)

DATEADD_DIFF(date, string)

DATEADD_DIFF(string, timestamp)

DATEADD_DIFF(timestamp, string)

Usage example

--disableDateAsTimestamp

Oracle

Snowflake

Known Issues

1. INTERVAL - INTERVAL Operation is not supported

Related EWIs

`--disableDateAsTimestamp`