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:

Oracle

IN -> Oracle_01.sql

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

Snowflake

OUT -> Oracle_01.sql

CREATE OR REPLACE TABLE TIMES (
  AsTimeStamp TIMESTAMP(6),
  AsTimestampTwo TIMESTAMP(6),
  AsDate TIMESTAMP /*** SSC-FDM-OR0042 - DATE TYPE COLUMN HAS A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/,
  AsDateTwo TIMESTAMP /*** SSC-FDM-OR0042 - DATE TYPE COLUMN HAS A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/
  )
  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'));

  CREATE OR REPLACE TABLE UNKNOWN_TABLE (
  Unknown TIMESTAMP(6)
  )
  COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
  ;

  INSERT INTO UNKNOWN_TABLE
  VALUES (
  TO_TIMESTAMP('01/10/09, 12:00 P.M.', 'dd/mm/yy, hh:mi P.M.')
);

Comparison Table

Oracle Queries

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;

Snowflake Operation

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;

Snowflake UDF

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;

Results

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

Known Issues

No issues were found.

Related EWIs

1. SSC-FDM-OR0042: Date Type Transformed To Timestamp Has A Different Behavior