⟩ How To Write Date and Time Interval Literals in Oracle?
Date and time interval literals can coded as shown in the following samples:
SELECT DATE '2002-10-03' + INTERVAL '123-2' YEAR(3) TO MONTH
FROM DUAL
-- 123 years and 2 months is added to 2002-10-03
03-DEC-25
SELECT DATE '2002-10-03' + INTERVAL '123' YEAR(3) FROM DUAL
-- 123 years is added to 2002-10-03
03-OCT-25
SELECT DATE '2002-10-03' + INTERVAL '299' MONTH(3) FROM DUAL
-- 299 months years is added to 2002-10-03
03-SEP-27
SELECT TIMESTAMP '1997-01-31 09:26:50.124'
+ INTERVAL '4 5:12:10.222' DAY TO SECOND(3) FROM DUAL
04-FEB-97 02.39.00.346000000 PM
SELECT TIMESTAMP '1997-01-31 09:26:50.124'
+ INTERVAL '4 5:12' DAY TO MINUTE FROM DUAL
04-FEB-97 02.38.50.124000000 PM
SELECT TIMESTAMP '1997-01-31 09:26:50.124'
+ INTERVAL '400 5' DAY(3) TO HOUR FROM DUAL
07-MAR-98 02.26.50.124000000 PM
SELECT TIMESTAMP '1997-01-31 09:26:50.124'
+ INTERVAL '400' DAY(3) FROM DUAL
07-MAR-98 09.26.50.124000000 AM
SELECT TIMESTAMP '1997-01-31 09:26:50.124'
+ INTERVAL '11:12:10.2222222' HOUR TO SECOND(7) FROM DUAL
31-JAN-97 08.39.00.346222200 PM
