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