DATEADD UDF INTERVAL
Description
This UDF is used to resolve operations with intervals like:
INTERVAL + DATE
INTERVAL + TIMESTAMP
DATE + INTERVAL
DATE + TIMESTAMP
INTERVAL + UNKNOWN
UNKNOWN + INTERVAL
Custom UDF overloads
DATEADD_UDF(string, date)
Parameters
INTERVAL_VALUE: The interval
String
of the operation.D: The
DATE
where the interval will be added.
CREATE OR REPLACE FUNCTION PUBLIC.DATEADD_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,PUBLIC.INTERVAL_TO_MONTHS_UDF(INPUT_VALUE),D)
WHEN INPUT_PART='MONTH' THEN
DATEADD(MONTHS,TO_NUMBER(INPUT_VALUE),D)
ELSE
DATEADD(MICROSECONDS,1000000*PUBLIC.INTERVAL_TO_SECONDS_UDF(INPUT_PART, INPUT_VALUE),D)::DATE
END CASE
FROM VARS
$$;
DATEADD_UDF(date, string)
Parameters
D: The
DATE
where the interval will be added.INTERVAL_VALUE: The interval
String
of the operation.
CREATE OR REPLACE FUNCTION PUBLIC.DATEADD_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,PUBLIC.INTERVAL_TO_MONTHS_UDF(INPUT_VALUE),D)
WHEN INPUT_PART='MONTH' THEN
DATEADD(MONTHS,TO_NUMBER(INPUT_VALUE),D)
ELSE
DATEADD(MICROSECONDS,1000000*PUBLIC.INTERVAL_TO_SECONDS_UDF(INPUT_PART, INPUT_VALUE),D)::DATE
END CASE
FROM VARS
$$;
DATEADD_UDF(string, timestamp)
Parameters
INTERVAL_VALUE: The interval
String
of the operation.D: The
TIMESTAMP
where the interval will be added.
CREATE OR REPLACE FUNCTION PUBLIC.DATEADD_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,PUBLIC.INTERVAL_TO_MONTHS_UDF(INPUT_VALUE),D)
WHEN INPUT_PART='MONTH' THEN
DATEADD(MONTHS,TO_NUMBER(INPUT_VALUE),D)
ELSE
DATEADD(MICROSECONDS,1000000*PUBLIC.INTERVAL_TO_SECONDS_UDF(INPUT_PART, INPUT_VALUE),D)
END CASE
FROM VARS
$$;
DATEADD_UDF(timestamp, string)
Parameters
D: The
TIMESTAMP
where the interval will be added.INTERVAL_VALUE: The interval
String
of the operation.
CREATE OR REPLACE FUNCTION PUBLIC.DATEADD_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,PUBLIC.INTERVAL_TO_MONTHS_UDF(INPUT_VALUE),D)
WHEN INPUT_PART='MONTH' THEN
DATEADD(MONTHS,TO_NUMBER(INPUT_VALUE),D)
ELSE
DATEADD(MICROSECONDS,1000000*PUBLIC.INTERVAL_TO_SECONDS_UDF(INPUT_PART, INPUT_VALUE),D)
END CASE
FROM VARS
$$;
Usage example
--disableDateAsTimestamp
--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
-- 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,
INTERVAL '1' MONTH+AsTimeStamp,
INTERVAL '1' MONTH+AsDate,
INTERVAL '1' MONTH+Unknown,
INTERVAL '2' MONTH+AsTimeStamp,
INTERVAL '2' MONTH+AsDate,
INTERVAL '2' MONTH+Unknown
FROM TIMES, UNKNOWN_TABLE;
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';
-- 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',
AsTimeStamp + INTERVAL '1 month',
AsDate + INTERVAL '1 month',
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '+' MAY NOT BEHAVE CORRECTLY BETWEEN Unknown AND Interval ***/!!!
Unknown + INTERVAL '1 month',
AsTimeStamp + INTERVAL '2 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 '2 month'
FROM
TIMES,
UNKNOWN_TABLE;
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.
Last updated