Date and Time Interval Literals in Oracle

Q

How To Write Date and Time Interval Literals in Oracle?

✍: FYIcenter.com

A

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 

SELECT TIMESTAMP '1997-01-31 09:26:50.124' 
  + INTERVAL '30.12345' SECOND(2,4) FROM DUAL
31-JAN-97 09.27.20.247500000 AM  

 

Understanding SQL Language Basics for Oracle

⇒⇒Oracle Database Tutorials

2016-10-17, 175👍, 0💬