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
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
DATE_ADD_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.DATE_ADD_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,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
$$;
DATE_ADD_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.DATE_ADD_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,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
$$;
DATE_ADD_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.DATE_ADD_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,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
$$;
DATE_ADD_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.DATE_ADD_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,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
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;
|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|INTERVAL'1'MONTH+ASTIMESTAMP|INTERVAL'1'MONTH+ASDATE|INTERVAL'1'MONTH+UNKNOWN|INTERVAL'2'MONTH+ASTIMESTAMP|INTERVAL'2'MONTH+ASDATE|INTERVAL'2'MONTH+UNKNOWN|
|---------------------------------------|---------------------------------------|----------------------------|----------------------------|----------------------------------|----------------------------------|-----------------------|-----------------------|---------------------------------------------|-----------------------------------|-----------------------------|----------------------------|-----------------------|------------------------|----------------------------|-----------------------|------------------------|
|2022-12-05 11:00:00.000 |2023-12-05 11:00:00.000 |2021-12-05 11:00:00.000 |2022-01-05 11:00:00.000 |2022-12-06 00:00:00.000 |2023-12-06 00:00:00.000 |2021-12-06 00:00:00.000|2022-01-06 00:00:00.000|2009-10-02 13:00:00.222 |2009-10-02 13:10:00.000 |2009-10-02 13:00:00.000 |2021-12-05 11:00:00.000 |2021-12-06 00:00:00.000|2009-11-01 12:00:00.000 |2022-01-05 11:00:00.000 |2022-01-06 00:00:00.000|2009-12-01 12: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';
-- 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',
AsTimeStamp + INTERVAL '1 month',
--** MSC-WARNING - MSCEWI3061 - COLUMN WAS TRANSFORMED FROM DATE TO TIMESTAMP. SOME OPERATIONS MAY BE AFFECTED **
AsDate + INTERVAL '1 month',
--** MSC-WARNING - MSCEWI3036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '+' MAY NOT BEHAVE CORRECTLY BETWEEN Unknown AND Interval **
Unknown + INTERVAL '1 month',
AsTimeStamp + INTERVAL '2 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 '2 month'
FROM
TIMES,
UNKNOWN_TABLE;
|DATEADD_UDF(ASTIMESTAMP,'INTERVAL ''1-1'' YEAR(2) TO MONTH')|DATEADD_UDF(ASTIMESTAMP,'INTERVAL ''2-1'' YEAR(4) TO MONTH')|DATEADD_UDF(ASTIMESTAMP,'INTERVAL ''1'' MONTH')|DATEADD_UDF(ASTIMESTAMP,'INTERVAL ''2'' MONTH')|DATEADD_UDF(ASDATE,'INTERVAL ''1-1'' YEAR(2) TO MONTH')|DATEADD_UDF(ASDATE,'INTERVAL ''2-1'' YEAR(4) TO MONTH')|DATEADD_UDF(ASDATE,'INTERVAL ''1'' MONTH')|DATEADD_UDF(ASDATE,'INTERVAL ''2'' MONTH')|DATEADD_UDF(UNKNOWN,'INTERVAL ''1 01:00:00.222'' DAY TO SECOND(3)')|DATEADD_UDF(UNKNOWN,'INTERVAL ''1 01:10'' DAY TO MINUTE')|DATEADD_UDF(UNKNOWN,'INTERVAL ''1 1'' DAY TO HOUR')|DATEADD_UDF('INTERVAL ''1'' MONTH',ASTIMESTAMP)|DATEADD_UDF('INTERVAL ''1'' MONTH',ASDATE)|DATEADD_UDF('INTERVAL ''1'' MONTH',UNKNOWN)|DATEADD_UDF('INTERVAL ''2'' MONTH',ASTIMESTAMP)|DATEADD_UDF('INTERVAL ''2'' MONTH',ASDATE)|DATEADD_UDF('INTERVAL ''2'' MONTH',UNKNOWN)|
|------------------------------------------------------------|------------------------------------------------------------|-----------------------------------------------|-----------------------------------------------|-------------------------------------------------------|-------------------------------------------------------|------------------------------------------|------------------------------------------|-------------------------------------------------------------------|---------------------------------------------------------|---------------------------------------------------|-----------------------------------------------|------------------------------------------|-------------------------------------------|-----------------------------------------------|------------------------------------------|-------------------------------------------|
|2022-12-05 11:00:00.000 |2023-12-05 11:00:00.000 |2021-12-05 11:00:00.000 |2022-01-05 11:00:00.000 |2022-12-06 |2023-12-06 |2021-12-06 |2022-01-06 |2009-10-02 13:00:00.222 |2009-10-02 13:10:00.000 |2009-10-02 13:00:00.000 |2021-12-05 11:00:00.000 |2021-12-06 |2009-11-01 12:00:00.000 |2022-01-05 11:00:00.000 |2022-01-06 |2009-12-01 12:00:00.000 |
Known Issues
1. INTERVAL + INTERVAL Operation is not supported
Snowflake does not support INTERVAL + INTERVAL operations.
Related EWIs
No related EWIs.
Last updated