DATEADD Function - Add Interval to Datetime - SQL Server to Oracle

In SQL Server you can use DATEADD function to add the specified number of units to a datetime value. In Oracle you have to use an INTERVAL expression.

SQL Server:

  -- Add 1 day to the current datetime
  SELECT DATEADD(DAY, 1, GETDATE());
  # 2023-02-11 17:19:59.360

Oracle:

  --  Add 1 day to the current datetime 
  SELECT SYSTIMESTAMP(3) + INTERVAL '1' DAY FROM dual;
  # 2023-02-11 17:19:59.360

You can map unit values between SQL Server DATEADD and Oracle INTERVAL literals as follows:

By default, the DAY INTERVAL precision is 2 in Oracle, so the following query fails:

Oracle:

  --  Exceeding the default precision 
  SELECT SYSTIMESTAMP + INTERVAL '100' DAY FROM dual;
  # ERROR at line 1:
  # ORA-01873: the leading precision of the interval is too small

So you have to specify the maximum precision DAY(3) explicitly:

  --  Specifying DAY(3) precision 
  SELECT SYSTIMESTAMP + INTERVAL '100' DAY(3) FROM dual;
  # 21-MAY-23 05.25.39.329000000 PM +01:00

At the same time, PL/SQL does not allow you to specify the precision:

  DECLARE
    dt TIMESTAMP;
  BEGIN
   dt :=  INTERVAL '100' DAY(3) + SYSTIMESTAMP; 
  END;
  /
  # ERROR at line 4:
  # ORA-06550: line 4, column 27:
  # PLS-00103: Encountered the symbol "("

And the following code works in PL/SQL while does not work in Oracle SQL:

  DECLARE
    dt TIMESTAMP;
  BEGIN
   dt :=  INTERVAL '100' DAY + SYSTIMESTAMP; 
  END;
  /
  # PL/SQL procedure successfully completed.

For more information, see SQL Server to Oracle Migration.