DATEDIFF UDF INTERVAL

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 used 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.DATE_DIFF_UDF(INTERVAL_VALUE STRING,D DATE)
RETURNS DATE
LANGUAGE SQL
IMMUTABLE
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.INTERVAL2MONTHS_UDF(INPUT_VALUE),D) 
    WHEN INPUT_PART='MONTH' THEN
        DATEADD(MONTHS,-1*TO_NUMBER(INPUT_VALUE),D)    
    ELSE
        DATEADD(MICROSECONDS,-1*1000000*PUBLIC.INTERVAL2SECONDS_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.DATE_DIFF_UDF(D DATE, INTERVAL_VALUE STRING)
RETURNS DATE
LANGUAGE SQL
IMMUTABLE
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.INTERVAL2MONTHS_UDF(INPUT_VALUE),D) 
    WHEN INPUT_PART='MONTH' THEN
        DATEADD(MONTHS,-1*TO_NUMBER(INPUT_VALUE),D)    
    ELSE
        DATEADD(MICROSECONDS,-1*1000000*PUBLIC.INTERVAL2SECONDS_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.DATE_DIFF_UDF(INTERVAL_VALUE STRING,D TIMESTAMP)
RETURNS TIMESTAMP
LANGUAGE SQL
IMMUTABLE
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.INTERVAL2MONTHS_UDF(INPUT_VALUE),D) 
    WHEN INPUT_PART='MONTH' THEN
        DATEADD(MONTHS,-1*TO_NUMBER(INPUT_VALUE),D)    
    ELSE
        DATEADD(MICROSECONDS,-1*1000000*PUBLIC.INTERVAL2SECONDS_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.DATE_DIFF_UDF(D TIMESTAMP, INTERVAL_VALUE STRING)
RETURNS TIMESTAMP
LANGUAGE SQL
IMMUTABLE
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.INTERVAL2MONTHS_UDF(INPUT_VALUE),D) 
    WHEN INPUT_PART='MONTH' THEN
        DATEADD(MONTHS,-1*TO_NUMBER(INPUT_VALUE),D)    
    ELSE
        DATEADD(MICROSECONDS,-1*1000000*PUBLIC.INTERVAL2SECONDS_UDF(INPUT_PART, INPUT_VALUE),D)
    END CASE    
FROM VARS    
$$;

Usage example

Oracle

Query

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

-- 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 TIMESTAMP /*** MSC-WARNING - MSCEWI3060 - DEFAULT VALUE FOR SYSDATE IS CURRENT_TIMESTAMP. COLUMN WAS TRANSFORMED TO TIMESTAMP TO PRESERVE INFORMATION. ***/,
 AsDateTwo TIMESTAMP /*** MSC-WARNING - MSCEWI3060 - DEFAULT VALUE FOR SYSDATE IS CURRENT_TIMESTAMP. COLUMN WAS TRANSFORMED TO TIMESTAMP TO PRESERVE INFORMATION. ***/
 );

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

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

SELECT
 AsTimeStamp - INTERVAL '1y, 1mm',
 AsTimeStamp - INTERVAL '2y, 1mm',
 AsTimeStamp - INTERVAL '1 month',
 AsTimeStamp - INTERVAL '2 month',
 --** MSC-WARNING - MSCEWI3061 - COLUMN WAS TRANSFORMED FROM DATE TO TIMESTAMP.  SOME OPERATIONS MAY BE AFFECTED **
 AsDate- INTERVAL '1y, 1mm',
 --** MSC-WARNING - MSCEWI3061 - COLUMN WAS TRANSFORMED FROM DATE TO TIMESTAMP.  SOME OPERATIONS MAY BE AFFECTED **
 AsDate- INTERVAL '2y, 1mm',
 --** MSC-WARNING - MSCEWI3061 - COLUMN WAS TRANSFORMED FROM DATE TO TIMESTAMP.  SOME OPERATIONS MAY BE AFFECTED **
 AsDate- INTERVAL '1 month',
 --** MSC-WARNING - MSCEWI3061 - COLUMN WAS TRANSFORMED FROM DATE TO TIMESTAMP.  SOME OPERATIONS MAY BE AFFECTED **
 AsDate- INTERVAL '2 month',
 --** MSC-WARNING - MSCEWI3036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '-' MAY NOT BEHAVE CORRECTLY BETWEEN Unknown AND Interval **
 Unknown - INTERVAL '1d, 01h, 00m, 00s, 222ms',
 --** MSC-WARNING - MSCEWI3036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '-' MAY NOT BEHAVE CORRECTLY BETWEEN Unknown AND Interval **
 Unknown - INTERVAL '1d, 01h, 10m',
 --** MSC-WARNING - MSCEWI3036 - 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

No related EWIs.