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

1. INTERVAL_VALUE: The interval String of the operation.

2. D: The DATE where the interval will be subtracted.

UDF

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

1. D: The DATE where the interval will be subtracted.

2. INTERVAL_VALUE: The interval String of the operation.

UDF

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

1. INTERVAL_VALUE: The interval String of the operation.

2. D: The TIMESTAMP where the interval will be subtracted.

UDF

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

1. D: The TIMESTAMP where the interval will be subtracted.

2. INTERVAL_VALUE: The interval String of the operation.

UDF

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

Query

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;

Result

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';

Query

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;

Result

Known Issues

1. INTERVAL - INTERVAL Operation is not supported

Snowflake does not support INTERVAL - INTERVAL operations.

Related EWIs

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