Interval UDFs vs Snowflake native interval operation
Description
The following table shows a comparison between the DATEADD_UDF INTERVAL and DATEDIFF_UDF INTERVAL vs the Snowflake native operation for interval arithmetic.
Necessary Code
To run the queries of the comparative table it is necessary to run the following code:
CREATE OR REPLACE 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'));
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 PUBLIC.TIMES (
AsTimeStamp TIMESTAMP(6),
AsTimestampTwo TIMESTAMP(6),
AsDate DATE,
AsDateTwo DATE);
INSERT INTO PUBLIC.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'));
CREATE OR REPLACE TABLE PUBLIC.UNKNOWN_TABLE (
Unknown TIMESTAMP(6));
INSERT INTO PUBLIC.UNKNOWN_TABLE VALUES (
TO_TIMESTAMP('01/10/09, 12:00 P.M.', 'dd/mm/yy, hh:mi P.M.')
);Comparison Table
SELECT AsTimeStamp+INTERVAL '1-1' YEAR(2) TO MONTH FROM TIMES;
SELECT AsTimeStamp-INTERVAL '1-1' YEAR(2) TO MONTH FROM TIMES;
SELECT AsTimeStamp+INTERVAL '2-1' YEAR(4) TO MONTH FROM TIMES;
SELECT AsTimeStamp-INTERVAL '2-1' YEAR(4) TO MONTH FROM TIMES;
SELECT AsTimeStamp+INTERVAL '1' MONTH FROM TIMES;
SELECT AsTimeStamp-INTERVAL '1' MONTH FROM TIMES;
SELECT AsTimeStamp+INTERVAL '2' MONTH FROM TIMES;
SELECT AsTimeStamp-INTERVAL '2' MONTH FROM TIMES;
SELECT AsTimeStamp+INTERVAL '1 01:00:00.222' DAY TO SECOND(3) FROM TIMES;
SELECT AsTimeStamp-INTERVAL '1 01:00:00.222' DAY TO SECOND(3) FROM TIMES;
SELECT AsTimeStamp+INTERVAL '1 01:10' DAY TO MINUTE FROM TIMES;
SELECT AsTimeStamp-INTERVAL '1 01:10' DAY TO MINUTE FROM TIMES;
SELECT AsTimeStamp+INTERVAL '1 1' DAY TO HOUR FROM TIMES;
SELECT AsTimeStamp-INTERVAL '1 1' DAY TO HOUR FROM TIMES;
SELECT AsTimeStamp+INTERVAL '10' DAY FROM TIMES;
SELECT AsTimeStamp-INTERVAL '10' DAY FROM TIMES;
SELECT AsTimeStamp+INTERVAL '3:05' HOUR TO MINUTE FROM TIMES;
SELECT AsTimeStamp-INTERVAL '3:05' HOUR TO MINUTE FROM TIMES;
SELECT AsTimeStamp+INTERVAL '5' HOUR FROM TIMES;
SELECT AsTimeStamp-INTERVAL '5' HOUR FROM TIMES;
SELECT AsTimeStamp+INTERVAL '5:10' MINUTE TO SECOND FROM TIMES;
SELECT AsTimeStamp-INTERVAL '5:10' MINUTE TO SECOND FROM TIMES;
SELECT AsTimeStamp+INTERVAL '30' MINUTE FROM TIMES;
SELECT AsTimeStamp-INTERVAL '30' MINUTE FROM TIMES;
SELECT AsTimeStamp+INTERVAL '333' HOUR(3) FROM TIMES;
SELECT AsTimeStamp-INTERVAL '333' HOUR(3) FROM TIMES;
SELECT AsTimeStamp+INTERVAL '15.6789' SECOND(2,3) FROM TIMES;
SELECT AsTimeStamp-INTERVAL '15.6789' SECOND(2,3) FROM TIMES;
SELECT AsDate+INTERVAL '1-1' YEAR(2) TO MONTH FROM TIMES;
SELECT AsDate-INTERVAL '1-1' YEAR(2) TO MONTH FROM TIMES;
SELECT AsDate+INTERVAL '2-1' YEAR(4) TO MONTH FROM TIMES;
SELECT AsDate-INTERVAL '2-1' YEAR(4) TO MONTH FROM TIMES;
SELECT AsDate+INTERVAL '1' MONTH FROM TIMES;
SELECT AsDate-INTERVAL '1' MONTH FROM TIMES;
SELECT AsDate+INTERVAL '2' MONTH FROM TIMES;
SELECT AsDate-INTERVAL '2' MONTH FROM TIMES;
SELECT AsDate+INTERVAL '1 01:00:00.222' DAY TO SECOND(3) FROM TIMES;
SELECT AsDate-INTERVAL '1 01:00:00.222' DAY TO SECOND(3) FROM TIMES;
SELECT AsDate+INTERVAL '1 01:10' DAY TO MINUTE FROM TIMES;
SELECT AsDate-INTERVAL '1 01:10' DAY TO MINUTE FROM TIMES;
SELECT AsDate+INTERVAL '1 1' DAY TO HOUR FROM TIMES;
SELECT AsDate-INTERVAL '1 1' DAY TO HOUR FROM TIMES;
SELECT AsDate+INTERVAL '10' DAY FROM TIMES;
SELECT AsDate-INTERVAL '10' DAY FROM TIMES;
SELECT AsDate+INTERVAL '3:05' HOUR TO MINUTE FROM TIMES;
SELECT AsDate-INTERVAL '3:05' HOUR TO MINUTE FROM TIMES;
SELECT AsDate+INTERVAL '5' HOUR FROM TIMES;
SELECT AsDate-INTERVAL '5' HOUR FROM TIMES;
SELECT AsDate+INTERVAL '5:10' MINUTE TO SECOND FROM TIMES;
SELECT AsDate-INTERVAL '5:10' MINUTE TO SECOND FROM TIMES;
SELECT AsDate+INTERVAL '30' MINUTE FROM TIMES;
SELECT AsDate-INTERVAL '30' MINUTE FROM TIMES;
SELECT AsDate+INTERVAL '333' HOUR(3) FROM TIMES;
SELECT AsDate-INTERVAL '333' HOUR(3) FROM TIMES;
SELECT AsDate+INTERVAL '15.6789' SECOND(2,3) FROM TIMES;
SELECT AsDate-INTERVAL '15.6789' SECOND(2,3) FROM TIMES;
SELECT Unknown+INTERVAL '1-1' YEAR(2) TO MONTH FROM UNKNOWN_TABLE;
SELECT Unknown-INTERVAL '1-1' YEAR(2) TO MONTH FROM UNKNOWN_TABLE;
SELECT Unknown+INTERVAL '2-1' YEAR(4) TO MONTH FROM UNKNOWN_TABLE;
SELECT Unknown-INTERVAL '2-1' YEAR(4) TO MONTH FROM UNKNOWN_TABLE;
SELECT Unknown+INTERVAL '1' MONTH FROM UNKNOWN_TABLE;
SELECT Unknown-INTERVAL '1' MONTH FROM UNKNOWN_TABLE;
SELECT Unknown+INTERVAL '2' MONTH FROM UNKNOWN_TABLE;
SELECT Unknown-INTERVAL '2' MONTH FROM UNKNOWN_TABLE;
SELECT Unknown+INTERVAL '1 01:00:00.222' DAY TO SECOND(3) FROM UNKNOWN_TABLE;
SELECT Unknown-INTERVAL '1 01:00:00.222' DAY TO SECOND(3) FROM UNKNOWN_TABLE;
SELECT Unknown+INTERVAL '1 01:10' DAY TO MINUTE FROM UNKNOWN_TABLE;
SELECT Unknown-INTERVAL '1 01:10' DAY TO MINUTE FROM UNKNOWN_TABLE;
SELECT Unknown+INTERVAL '1 1' DAY TO HOUR FROM UNKNOWN_TABLE;
SELECT Unknown-INTERVAL '1 1' DAY TO HOUR FROM UNKNOWN_TABLE;
SELECT Unknown+INTERVAL '10' DAY FROM UNKNOWN_TABLE;
SELECT Unknown-INTERVAL '10' DAY FROM UNKNOWN_TABLE;
SELECT Unknown+INTERVAL '3:05' HOUR TO MINUTE FROM UNKNOWN_TABLE;
SELECT Unknown-INTERVAL '3:05' HOUR TO MINUTE FROM UNKNOWN_TABLE;
SELECT Unknown+INTERVAL '5' HOUR FROM UNKNOWN_TABLE;
SELECT Unknown-INTERVAL '5' HOUR FROM UNKNOWN_TABLE;
SELECT Unknown+INTERVAL '5:10' MINUTE TO SECOND FROM UNKNOWN_TABLE;
SELECT Unknown-INTERVAL '5:10' MINUTE TO SECOND FROM UNKNOWN_TABLE;
SELECT Unknown+INTERVAL '30' MINUTE FROM UNKNOWN_TABLE;
SELECT Unknown-INTERVAL '30' MINUTE FROM UNKNOWN_TABLE;
SELECT Unknown+INTERVAL '333' HOUR(3) FROM UNKNOWN_TABLE;
SELECT Unknown-INTERVAL '333' HOUR(3) FROM UNKNOWN_TABLE;
SELECT Unknown+INTERVAL '15.6789' SECOND(2,3) FROM UNKNOWN_TABLE;
SELECT Unknown-INTERVAL '15.6789' SECOND(2,3) FROM UNKNOWN_TABLE;
SELECT INTERVAL '1-1' YEAR(2) TO MONTH+ AsTimeStamp FROM TIMES;
SELECT INTERVAL '1-1' YEAR(2) TO MONTH+AsDate FROM TIMES;
SELECT INTERVAL '1-1' YEAR(2) TO MONTH+Unknown FROM UNKNOWN_TABLE;
SELECT INTERVAL '2-1' YEAR(4) TO MONTH+AsTimeStamp FROM TIMES;
SELECT INTERVAL '2-1' YEAR(4) TO MONTH+AsDate FROM TIMES;
SELECT INTERVAL '2-1' YEAR(4) TO MONTH+Unknown FROM UNKNOWN_TABLE;
SELECT INTERVAL '1' MONTH+AsTimeStamp FROM TIMES;
SELECT INTERVAL '1' MONTH+AsDate FROM TIMES;
SELECT INTERVAL '1' MONTH+Unknown FROM UNKNOWN_TABLE;
SELECT INTERVAL '2' MONTH+AsTimeStamp FROM TIMES;
SELECT INTERVAL '2' MONTH+AsDate FROM TIMES;
SELECT INTERVAL '2' MONTH+Unknown FROM UNKNOWN_TABLE;
SELECT INTERVAL '1 01:00:00.222' DAY TO SECOND(3)+AsTimeStamp FROM TIMES;
SELECT INTERVAL '1 01:00:00.222' DAY TO SECOND(3)+AsDate FROM TIMES;
SELECT INTERVAL '1 01:00:00.222' DAY TO SECOND(3)+Unknown FROM UNKNOWN_TABLE;
SELECT INTERVAL '1 01:10' DAY TO MINUTE+AsTimeStamp FROM TIMES;
SELECT INTERVAL '1 01:10' DAY TO MINUTE+AsDate FROM TIMES;
SELECT INTERVAL '1 01:10' DAY TO MINUTE+Unknown FROM UNKNOWN_TABLE;
SELECT INTERVAL '1 1' DAY TO HOUR+AsTimeStamp FROM TIMES;
SELECT INTERVAL '1 1' DAY TO HOUR+AsDate FROM TIMES;
SELECT INTERVAL '1 1' DAY TO HOUR+Unknown FROM UNKNOWN_TABLE;
SELECT INTERVAL '10' DAY+AsTimeStamp FROM TIMES;
SELECT INTERVAL '10' DAY+AsDate FROM TIMES;
SELECT INTERVAL '10' DAY+Unknown FROM UNKNOWN_TABLE;
SELECT INTERVAL '3:05' HOUR TO MINUTE+AsTimeStamp FROM TIMES;
SELECT INTERVAL '3:05' HOUR TO MINUTE+AsDate FROM TIMES;
SELECT INTERVAL '3:05' HOUR TO MINUTE+Unknown FROM UNKNOWN_TABLE;
SELECT INTERVAL '5' HOUR+AsTimeStamp FROM TIMES;
SELECT INTERVAL '5' HOUR+AsDate FROM TIMES;
SELECT INTERVAL '5' HOUR+Unknown FROM UNKNOWN_TABLE;
SELECT INTERVAL '5:10' MINUTE TO SECOND+AsTimeStamp FROM TIMES;
SELECT INTERVAL '5:10' MINUTE TO SECOND+AsDate FROM TIMES;
SELECT INTERVAL '5:10' MINUTE TO SECOND+Unknown FROM UNKNOWN_TABLE;
SELECT INTERVAL '30' MINUTE+AsTimeStamp FROM TIMES;
SELECT INTERVAL '30' MINUTE+AsDate FROM TIMES;
SELECT INTERVAL '30' MINUTE+Unknown FROM UNKNOWN_TABLE;
SELECT INTERVAL '333' HOUR(3)+AsTimeStamp FROM TIMES;
SELECT INTERVAL '333' HOUR(3)+AsDate FROM TIMES;
SELECT INTERVAL '333' HOUR(3)+Unknown FROM UNKNOWN_TABLE;
SELECT INTERVAL '15.6789' SECOND(2,3)+AsTimeStamp FROM TIMES;
SELECT INTERVAL '15.6789' SECOND(2,3)+AsDate FROM TIMES;
SELECT INTERVAL '15.6789' SECOND(2,3)+Unknown FROM UNKNOWN_TABLE;SELECT AsTimeStamp + INTERVAL '1y, 1mm' FROM PUBLIC.TIMES;
SELECT AsTimeStamp - INTERVAL '1y, 1mm' FROM PUBLIC.TIMES;
SELECT AsTimeStamp + INTERVAL '2y, 1mm' FROM PUBLIC.TIMES;
SELECT AsTimeStamp - INTERVAL '2y, 1mm' FROM PUBLIC.TIMES;
SELECT AsTimeStamp + INTERVAL '1 month' FROM PUBLIC.TIMES;
SELECT AsTimeStamp - INTERVAL '1 month' FROM PUBLIC.TIMES;
SELECT AsTimeStamp + INTERVAL '2 month' FROM PUBLIC.TIMES;
SELECT AsTimeStamp - INTERVAL '2 month' FROM PUBLIC.TIMES;
SELECT AsTimeStamp + INTERVAL '1d, 01h, 00m, 00s, 222ms' FROM PUBLIC.TIMES;
SELECT AsTimeStamp - INTERVAL '1d, 01h, 00m, 00s, 222ms' FROM PUBLIC.TIMES;
SELECT AsTimeStamp + INTERVAL '1d, 01h, 10m' FROM PUBLIC.TIMES;
SELECT AsTimeStamp - INTERVAL '1d, 01h, 10m' FROM PUBLIC.TIMES;
SELECT AsTimeStamp + INTERVAL '1d, 1h' FROM PUBLIC.TIMES;
SELECT AsTimeStamp - INTERVAL '1d, 1h' FROM PUBLIC.TIMES;
SELECT AsTimeStamp + INTERVAL '10 day' FROM PUBLIC.TIMES;
SELECT AsTimeStamp - INTERVAL '10 day' FROM PUBLIC.TIMES;
SELECT AsTimeStamp + INTERVAL '3h, 05m' FROM PUBLIC.TIMES;
SELECT AsTimeStamp - INTERVAL '3h, 05m' FROM PUBLIC.TIMES;
SELECT AsTimeStamp + INTERVAL '5 hour' FROM PUBLIC.TIMES;
SELECT AsTimeStamp - INTERVAL '5 hour' FROM PUBLIC.TIMES;
SELECT AsTimeStamp + INTERVAL '5m, 10s' FROM PUBLIC.TIMES;
SELECT AsTimeStamp - INTERVAL '5m, 10s' FROM PUBLIC.TIMES;
SELECT AsTimeStamp + INTERVAL '30 minute' FROM PUBLIC.TIMES;
SELECT AsTimeStamp - INTERVAL '30 minute' FROM PUBLIC.TIMES;
SELECT AsTimeStamp + INTERVAL '333 hour' FROM PUBLIC.TIMES;
SELECT AsTimeStamp - INTERVAL '333 hour' FROM PUBLIC.TIMES;
SELECT AsTimeStamp + INTERVAL '15.6789 second' FROM PUBLIC.TIMES;
SELECT AsTimeStamp - INTERVAL '15.6789 second' FROM PUBLIC.TIMES;
SELECT AsDate + INTERVAL '1y, 1mm' FROM PUBLIC.TIMES;
SELECT AsDate - INTERVAL '1y, 1mm' FROM PUBLIC.TIMES;
SELECT AsDate + INTERVAL '2y, 1mm' FROM PUBLIC.TIMES;
SELECT AsDate - INTERVAL '2y, 1mm' FROM PUBLIC.TIMES;
SELECT AsDate + INTERVAL '1 month' FROM PUBLIC.TIMES;
SELECT AsDate - INTERVAL '1 month' FROM PUBLIC.TIMES;
SELECT AsDate + INTERVAL '2 month' FROM PUBLIC.TIMES;
SELECT AsDate - INTERVAL '2 month' FROM PUBLIC.TIMES;
SELECT AsDate + INTERVAL '1d, 01h, 00m, 00s, 222ms' FROM PUBLIC.TIMES;
SELECT AsDate - INTERVAL '1d, 01h, 00m, 00s, 222ms' FROM PUBLIC.TIMES;
SELECT AsDate + INTERVAL '1d, 01h, 10m' FROM PUBLIC.TIMES;
SELECT AsDate - INTERVAL '1d, 01h, 10m' FROM PUBLIC.TIMES;
SELECT AsDate + INTERVAL '1d, 1h' FROM PUBLIC.TIMES;
SELECT AsDate - INTERVAL '1d, 1h' FROM PUBLIC.TIMES;
SELECT AsDate + INTERVAL '10 day' FROM PUBLIC.TIMES;
SELECT AsDate - INTERVAL '10 day' FROM PUBLIC.TIMES;
SELECT AsDate + INTERVAL '3h, 05m' FROM PUBLIC.TIMES;
SELECT AsDate - INTERVAL '3h, 05m' FROM PUBLIC.TIMES;
SELECT AsDate + INTERVAL '5 hour' FROM PUBLIC.TIMES;
SELECT AsDate - INTERVAL '5 hour' FROM PUBLIC.TIMES;
SELECT AsDate + INTERVAL '5m, 10s' FROM PUBLIC.TIMES;
SELECT AsDate - INTERVAL '5m, 10s' FROM PUBLIC.TIMES;
SELECT AsDate + INTERVAL '30 minute' FROM PUBLIC.TIMES;
SELECT AsDate - INTERVAL '30 minute' FROM PUBLIC.TIMES;
SELECT AsDate + INTERVAL '333 hour' FROM PUBLIC.TIMES;
SELECT AsDate - INTERVAL '333 hour' FROM PUBLIC.TIMES;
SELECT AsDate + INTERVAL '15.6789 second' FROM PUBLIC.TIMES;
SELECT AsDate - INTERVAL '15.6789 second' FROM PUBLIC.TIMES;
SELECT Unknown + INTERVAL '1y, 1mm' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown - INTERVAL '1y, 1mm' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown + INTERVAL '2y, 1mm' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown - INTERVAL '2y, 1mm' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown + INTERVAL '1 month' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown - INTERVAL '1 month' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown + INTERVAL '2 month' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown - INTERVAL '2 month' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown + INTERVAL '1d, 01h, 00m, 00s, 222ms' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown - INTERVAL '1d, 01h, 00m, 00s, 222ms' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown + INTERVAL '1d, 01h, 10m' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown - INTERVAL '1d, 01h, 10m' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown + INTERVAL '1d, 1h' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown - INTERVAL '1d, 1h' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown + INTERVAL '10 day' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown - INTERVAL '10 day' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown + INTERVAL '3h, 05m' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown - INTERVAL '3h, 05m' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown + INTERVAL '5 hour' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown - INTERVAL '5 hour' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown + INTERVAL '5m, 10s' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown - INTERVAL '5m, 10s' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown + INTERVAL '30 minute' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown - INTERVAL '30 minute' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown + INTERVAL '333 hour' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown - INTERVAL '333 hour' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown + INTERVAL '15.6789 second' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown - INTERVAL '15.6789 second' FROM PUBLIC.UNKNOWN_TABLE;SELECT DATEADD_UDF(AsTimeStamp,'INTERVAL ''1-1'' YEAR(2) TO MONTH') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsTimeStamp,'INTERVAL ''1-1'' YEAR(2) TO MONTH') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsTimeStamp,'INTERVAL ''2-1'' YEAR(4) TO MONTH') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsTimeStamp,'INTERVAL ''2-1'' YEAR(4) TO MONTH') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsTimeStamp,'INTERVAL ''1'' MONTH') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsTimeStamp,'INTERVAL ''1'' MONTH') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsTimeStamp,'INTERVAL ''2'' MONTH') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsTimeStamp,'INTERVAL ''2'' MONTH') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsTimeStamp,'INTERVAL ''1 01:00:00.222'' DAY TO SECOND(3)') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsTimeStamp,'INTERVAL ''1 01:00:00.222'' DAY TO SECOND(3)') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsTimeStamp,'INTERVAL ''1 01:10'' DAY TO MINUTE') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsTimeStamp,'INTERVAL ''1 01:10'' DAY TO MINUTE') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsTimeStamp,'INTERVAL ''1 1'' DAY TO HOUR') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsTimeStamp,'INTERVAL ''1 1'' DAY TO HOUR') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsTimeStamp,'INTERVAL ''10'' DAY') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsTimeStamp,'INTERVAL ''10'' DAY') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsTimeStamp,'INTERVAL ''3:05'' HOUR TO MINUTE') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsTimeStamp,'INTERVAL ''3:05'' HOUR TO MINUTE') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsTimeStamp,'INTERVAL ''5'' HOUR') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsTimeStamp,'INTERVAL ''5'' HOUR') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsTimeStamp,'INTERVAL ''5:10'' MINUTE TO SECOND') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsTimeStamp,'INTERVAL ''5:10'' MINUTE TO SECOND') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsTimeStamp,'INTERVAL ''30'' MINUTE') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsTimeStamp,'INTERVAL ''30'' MINUTE') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsTimeStamp,'INTERVAL ''333'' HOUR(3)') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsTimeStamp,'INTERVAL ''333'' HOUR(3)') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsTimeStamp,'INTERVAL ''15.6789'' SECOND(2,3)') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsTimeStamp,'INTERVAL ''15.6789'' SECOND(2,3)') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsDate,'INTERVAL ''1-1'' YEAR(2) TO MONTH') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsDate,'INTERVAL ''1-1'' YEAR(2) TO MONTH') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsDate,'INTERVAL ''2-1'' YEAR(4) TO MONTH') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsDate,'INTERVAL ''2-1'' YEAR(4) TO MONTH') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsDate,'INTERVAL ''1'' MONTH') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsDate,'INTERVAL ''1'' MONTH') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsDate,'INTERVAL ''2'' MONTH') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsDate,'INTERVAL ''2'' MONTH') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsDate,'INTERVAL ''1 01:00:00.222'' DAY TO SECOND(3)') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsDate,'INTERVAL ''1 01:00:00.222'' DAY TO SECOND(3)') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsDate,'INTERVAL ''1 01:10'' DAY TO MINUTE') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsDate,'INTERVAL ''1 01:10'' DAY TO MINUTE') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsDate,'INTERVAL ''1 1'' DAY TO HOUR') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsDate,'INTERVAL ''1 1'' DAY TO HOUR') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsDate,'INTERVAL ''10'' DAY') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsDate,'INTERVAL ''10'' DAY') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsDate,'INTERVAL ''3:05'' HOUR TO MINUTE') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsDate,'INTERVAL ''3:05'' HOUR TO MINUTE') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsDate,'INTERVAL ''5'' HOUR') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsDate,'INTERVAL ''5'' HOUR') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsDate,'INTERVAL ''5:10'' MINUTE TO SECOND') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsDate,'INTERVAL ''5:10'' MINUTE TO SECOND') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsDate,'INTERVAL ''30'' MINUTE') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsDate,'INTERVAL ''30'' MINUTE') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsDate,'INTERVAL ''333'' HOUR(3)') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsDate,'INTERVAL ''333'' HOUR(3)') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsDate,'INTERVAL ''15.6789'' SECOND(2,3)') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsDate,'INTERVAL ''15.6789'' SECOND(2,3)') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(UnKnown,'INTERVAL ''1-1'' YEAR(2) TO MONTH') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEDIFF_UDF(UnKnown,'INTERVAL ''1-1'' YEAR(2) TO MONTH') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEADD_UDF(UnKnown,'INTERVAL ''2-1'' YEAR(4) TO MONTH') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEDIFF_UDF(UnKnown,'INTERVAL ''2-1'' YEAR(4) TO MONTH') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEADD_UDF(UnKnown,'INTERVAL ''1'' MONTH') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEDIFF_UDF(UnKnown,'INTERVAL ''1'' MONTH') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEADD_UDF(UnKnown,'INTERVAL ''2'' MONTH') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEDIFF_UDF(UnKnown,'INTERVAL ''2'' MONTH') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEADD_UDF(UnKnown,'INTERVAL ''1 01:00:00.222'' DAY TO SECOND(3)') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEDIFF_UDF(UnKnown,'INTERVAL ''1 01:00:00.222'' DAY TO SECOND(3)') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEADD_UDF(UnKnown,'INTERVAL ''1 01:10'' DAY TO MINUTE') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEDIFF_UDF(UnKnown,'INTERVAL ''1 01:10'' DAY TO MINUTE') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEADD_UDF(UnKnown,'INTERVAL ''1 1'' DAY TO HOUR') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEDIFF_UDF(UnKnown,'INTERVAL ''1 1'' DAY TO HOUR') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEADD_UDF(UnKnown,'INTERVAL ''10'' DAY') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEDIFF_UDF(UnKnown,'INTERVAL ''10'' DAY') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEADD_UDF(UnKnown,'INTERVAL ''3:05'' HOUR TO MINUTE') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEDIFF_UDF(UnKnown,'INTERVAL ''3:05'' HOUR TO MINUTE') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEADD_UDF(UnKnown,'INTERVAL ''5'' HOUR') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEDIFF_UDF(UnKnown,'INTERVAL ''5'' HOUR') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEADD_UDF(UnKnown,'INTERVAL ''5:10'' MINUTE TO SECOND') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEDIFF_UDF(UnKnown,'INTERVAL ''5:10'' MINUTE TO SECOND') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEADD_UDF(UnKnown,'INTERVAL ''30'' MINUTE') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEDIFF_UDF(UnKnown,'INTERVAL ''30'' MINUTE') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEADD_UDF(UnKnown,'INTERVAL ''333'' HOUR(3)') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEDIFF_UDF(UnKnown,'INTERVAL ''333'' HOUR(3)') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEADD_UDF(UnKnown,'INTERVAL ''15.6789'' SECOND(2,3)') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEDIFF_UDF(UnKnown,'INTERVAL ''15.6789'' SECOND(2,3)') FROM PUBLIC.UNKNOWN_TABLE;| Oracle | Snowflake Operation | UDF |
|---|---|---|
2022-12-05 11:00:00.000 | 2022-12-05 11:00:00.000 | 2022-12-05 11:00:00.000 |
2020-10-05 11:00:00.000 | 2020-10-05 11:00:00.000 | 2020-10-05 11:00:00.000 |
2023-12-05 11:00:00.000 | 2023-12-05 11:00:00.000 | 2023-12-05 11:00:00.000 |
2019-10-05 11:00:00.000 | 2019-10-05 11:00:00.000 | 2019-10-05 11:00:00.000 |
2021-12-05 11:00:00.000 | 2021-12-05 11:00:00.000 | 2021-12-05 11:00:00.000 |
2021-10-05 11:00:00.000 | 2021-10-05 11:00:00.000 | 2021-10-05 11:00:00.000 |
2022-01-05 11:00:00.000 | 2022-01-05 11:00:00.000 | 2022-01-05 11:00:00.000 |
2021-09-05 11:00:00.000 | 2021-09-05 11:00:00.000 | 2021-09-05 11:00:00.000 |
2021-11-06 12:00:00.222 | 2021-11-06 12:00:00.222 | 2021-11-06 12:00:00.222 |
2021-11-04 09:59:59.778 | 2021-11-04 09:59:59.778 | 2021-11-04 09:59:59.778 |
2021-11-06 12:10:00.000 | 2021-11-06 12:10:00.000 | 2021-11-06 12:10:00.000 |
2021-11-04 09:50:00.000 | 2021-11-04 09:50:00.000 | 2021-11-04 09:50:00.000 |
2021-11-06 12:00:00.000 | 2021-11-06 12:00:00.000 | 2021-11-06 12:00:00.000 |
2021-11-04 10:00:00.000 | 2021-11-04 10:00:00.000 | 2021-11-04 10:00:00.000 |
2021-11-15 11:00:00.000 | 2021-11-15 11:00:00.000 | 2021-11-15 11:00:00.000 |
2021-10-26 11:00:00.000 | 2021-10-26 11:00:00.000 | 2021-10-26 11:00:00.000 |
2021-11-05 14:05:00.000 | 2021-11-05 14:05:00.000 | 2021-11-05 14:05:00.000 |
2021-11-05 07:55:00.000 | 2021-11-05 07:55:00.000 | 2021-11-05 07:55:00.000 |
2021-11-05 16:00:00.000 | 2021-11-05 16:00:00.000 | 2021-11-05 16:00:00.000 |
2021-11-05 06:00:00.000 | 2021-11-05 06:00:00.000 | 2021-11-05 06:00:00.000 |
2021-11-05 11:05:10.000 | 2021-11-05 11:05:10.000 | 2021-11-05 11:05:10.000 |
2021-11-05 10:54:50.000 | 2021-11-05 10:54:50.000 | 2021-11-05 10:54:50.000 |
2021-11-05 11:30:00.000 | 2021-11-05 11:30:00.000 | 2021-11-05 11:30:00.000 |
2021-11-05 10:30:00.000 | 2021-11-05 10:30:00.000 | 2021-11-05 10:30:00.000 |
2021-11-19 08:00:00.000 | 2021-11-19 08:00:00.000 | 2021-11-19 08:00:00.000 |
2021-10-22 14:00:00.000 | 2021-10-22 14:00:00.000 | 2021-10-22 14:00:00.000 |
2021-11-05 11:00:15.679 | 2021-11-05 11:00:16.000 | 2021-11-05 11:00:15.678 |
2021-11-05 10:59:44.321 | 2021-11-05 10:59:44.000 | 2021-11-05 11:00:15.678 |
2022-12-06 00:00:00.000 | 2022-12-06 | 2022-12-06 |
2020-10-06 00:00:00.000 | 2020-10-06 | 2020-10-06 |
2023-12-06 00:00:00.000 | 2023-12-06 | 2023-12-06 |
2019-10-06 00:00:00.000 | 2019-10-06 | 2019-10-06 |
2021-12-06 00:00:00.000 | 2021-12-06 | 2021-12-06 |
2021-12-06 00:00:00.000 | 2021-10-06 | 2021-10-06 |
2022-01-06 00:00:00.000 | 2022-01-06 | 2022-01-06 |
2021-09-06 00:00:00.000 | 2021-09-06 | 2021-09-06 |
2021-11-07 01:00:00.000 | 2021-11-07 01:00:00.222 | 2021-11-07 |
2021-11-04 22:59:59.000 | 2021-11-04 22:59:59.778 | 2021-11-04 |
2021-11-07 01:10:00.000 | 2021-11-07 01:10:00.000 | 2021-11-07 |
2021-11-04 22:50:00.000 | 2021-11-04 22:50:00.000 | 2021-11-04 |
2021-11-07 01:00:00.000 | 2021-11-07 01:00:00.000 | 2021-11-07 |
2021-11-04 23:00:00.000 | 2021-11-04 23:00:00.000 | 2021-11-04 |
2021-11-16 00:00:00.000 | 2021-11-16 | 2021-11-16 |
2021-10-27 00:00:00.000 | 2021-10-27 | 2021-10-27 |
2021-11-06 03:05:00.000 | 2021-11-06 03:05:00.000 | 2021-11-06 |
2021-11-05 20:55:00.000 | 2021-11-05 20:55:00.000 | 2021-11-05 |
2021-11-06 05:00:00.000 | 2021-11-06 05:00:00.000 | 2021-11-06 |
2021-11-05 19:00:00.000 | 2021-11-05 19:00:00.000 | 2021-11-05 |
2021-11-06 00:05:10.000 | 2021-11-06 00:05:10.000 | 2021-11-06 |
2021-11-05 23:54:50.000 | 2021-11-05 23:54:50.000 | 2021-11-05 |
2021-11-06 00:30:00.000 | 2021-11-06 00:30:00.000 | 2021-11-06 |
2021-11-05 23:30:00.000 | 2021-11-05 23:30:00.000 | 2021-11-05 |
2021-11-19 21:00:00.000 | 2021-11-19 21:00:00.000 | 2021-11-19 |
2021-10-23 03:00:00.000 | 2021-10-23 03:00:00.000 | 2021-10-23 |
2021-11-06 00:00:15.000 | 2021-11-06 00:00:16.000 | 2021-11-06 |
2021-11-05 23:59:44.000 | 2021-11-05 23:59:44.000 | 2021-11-05 |
2010-11-01 12:00:00.000 | 2010-11-01 12:00:00.000 | 2010-11-01 12:00:00.000 |
2008-09-01 12:00:00.000 | 2008-09-01 12:00:00.000 | 2008-09-01 12:00:00.000 |
2011-11-01 12:00:00.000 | 2011-11-01 12:00:00.000 | 2011-11-01 12:00:00.000 |
2007-09-01 12:00:00.000 | 2007-09-01 12:00:00.000 | 2007-09-01 12:00:00.000 |
2009-11-01 12:00:00.000 | 2009-11-01 12:00:00.000 | 2009-11-01 12:00:00.000 |
2009-09-01 12:00:00.000 | 2009-09-01 12:00:00.000 | 2009-09-01 12:00:00.000 |
2009-12-01 12:00:00.000 | 2009-12-01 12:00:00.000 | 2009-12-01 12:00:00.000 |
2009-08-01 12:00:00.000 | 2009-08-01 12:00:00.000 | 2009-08-01 12:00:00.000 |
2009-10-02 13:00:00.222 | 2009-10-02 13:00:00.222 | 2009-10-02 13:00:00.222 |
2009-09-30 10:59:59.778 | 2009-09-30 10:59:59.778 | 2009-09-30 10:59:59.778 |
2009-10-02 13:10:00.000 | 2009-10-02 13:10:00.000 | 2009-10-02 13:10:00.000 |
2009-09-30 10:50:00.000 | 2009-09-30 10:50:00.000 | 2009-09-30 10:50:00.000 |
2009-10-02 13:00:00.000 | 2009-10-02 13:00:00.000 | 2009-10-02 13:00:00.000 |
2009-09-30 11:00:00.000 | 2009-09-30 11:00:00.000 | 2009-09-30 11:00:00.000 |
2009-10-11 12:00:00.000 | 2009-10-11 12:00:00.000 | 2009-10-11 12:00:00.000 |
2009-09-21 12:00:00.000 | 2009-09-21 12:00:00.000 | 2009-09-21 12:00:00.000 |
2009-10-01 15:05:00.000 | 2009-10-01 15:05:00.000 | 2009-10-01 15:05:00.000 |
2009-10-01 08:55:00.000 | 2009-10-01 08:55:00.000 | 2009-10-01 08:55:00.000 |
2009-10-01 17:00:00.000 | 2009-10-01 17:00:00.000 | 2009-10-01 17:00:00.000 |
2009-10-01 07:00:00.000 | 2009-10-01 07:00:00.000 | 2009-10-01 07:00:00.000 |
2009-10-01 12:05:10.000 | 2009-10-01 12:05:10.000 | 2009-10-01 12:05:10.000 |
2009-10-01 11:54:50.000 | 2009-10-01 11:54:50.000 | 2009-10-01 11:54:50.000 |
2009-10-01 12:30:00.000 | 2009-10-01 12:30:00.000 | 2009-10-01 12:30:00.000 |
2009-10-01 11:30:00.000 | 2009-10-01 11:30:00.000 | 2009-10-01 11:30:00.000 |
2009-10-15 09:00:00.000 | 2009-10-15 09:00:00.000 | 2009-10-15 09:00:00.000 |
2009-09-17 15:00:00.000 | 2009-09-17 15:00:00.000 | 2009-09-17 15:00:00.000 |
2009-10-01 12:00:15.679 | 2009-10-01 12:00:16.000 | 2009-10-01 12:00:15.678 |
2009-10-01 11:59:44.321 | 2009-10-01 11:59:44.000 | 2009-10-01 11:59:44.321 |
Last updated