Date and Time Differences in Oracle

Q

How To Calculate Date and Time Differences in Oracle?

✍: FYIcenter.com

A

If you want to know how many years, months, days and seconds are there between two dates or times, you can use the date and time interval expressions: YEAR ... TO MONTH and DAY ... TO SECOND. The tutorial exercise below gives you some good examples:

SELECT
  (TO_DATE('01-MAY-2006 16:52:57','DD-MON-YYYY HH24:MI:SS')
   - 
   TO_DATE('31-JAN-1897 09:26:50','DD-MON-YYYY HH24:MI:SS'))
  YEAR(4) TO MONTH FROM DUAL;
  -- 109 years and 3 months
109-3  

SELECT 
  (TO_DATE('01-MAY-2006 16:52:57','DD-MON-YYYY HH24:MI:SS')
   - 
   TO_DATE('31-JAN-1897 09:26:50','DD-MON-YYYY HH24:MI:SS'))
  DAY(9) TO SECOND FROM DUAL;
  -- 39901 days and some seconds
39901 7:26:7.0  

SELECT 
  (TO_TIMESTAMP('01-MAY-2006 16:52:57.847',
  'DD-MON-YYYY HH24:MI:SS.FF3') - 
   TO_TIMESTAMP('31-JAN-1897 09:26:50.124',
  'DD-MON-YYYY HH24:MI:SS.FF3'))
  YEAR(4) TO MONTH FROM DUAL;
  -- 109 years and 3 months
109-3  

SELECT 
  (TO_TIMESTAMP('01-MAY-2006 16:52:57.847',
  'DD-MON-YYYY HH24:MI:SS.FF3') - 
   TO_TIMESTAMP('31-JAN-1897 09:26:50.124',
  'DD-MON-YYYY HH24:MI:SS.FF3'))
  DAY(9) TO SECOND
  FROM DUAL;
  -- 39901 days and some fractional seconds
39901 7:26:7.723000000 

 

Understanding SQL Language Basics for Oracle

⇒⇒Oracle Database Tutorials

2016-10-17, 169👍, 0💬