How To Write Date and Time Interval Literals

Q

How To Write Date and Time Interval Literals? - Oracle DBA FAQ - Understanding SQL Basics

✍: 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
```

2007-04-23, 5268👍, 0💬