Home >> FAQs/Tutorials >> Oracle Tutorials

Oracle Tutorials - Date and Time Differences

By: FYIcenter.com

(Continued from previous topic...)

How To Calculate Date and Time Differences?

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 



(Continued on next topic...)

  1. What Is SQL Standard?
  2. How Many Categories of Data Types?
  3. What Are the Oracle Built-in Data Types?
  4. What Are the Differences between CHAR and NCHAR?
  5. What Are the Differences between CHAR and VARCHAR2?
  6. What Are the Differences between NUMBER and BINARY_FLOAT?
  7. What Are the Differences between DATE and TIMESTAMP?
  8. What Are the Differences between INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND?
  9. What Are the Differences between BLOB and CLOB?
  10. What Are the ANSI Data Types Supported in Oracle?
  11. How To Write Text Literals?
  12. How To Write Numeric Literals?
  13. How To Write Date and Time Literals?
  14. How To Write Date and Time Interval Literals?
  15. How To Convert Numbers to Character Strings?
  16. How To Convert Characters to Numbers?
  17. How To Convert Dates to Characters?
  18. How To Convert Character Strings to Dates?
  19. How To Convert Times to Character Strings?
  20. How To Convert Character Strings to Times?
  21. What Is NULL Value?
  22. How To Use NULL as Conditions?
  23. How To Concatenate Two Text Values?
  24. How To Increment Dates by 1?
  25. How To Calculate Date and Time Differences?
  26. How To Use IN Conditions?
  27. How To Use LIKE Conditions?
  28. How To Use Regular Expression in Pattern Match Conditions?

Oracle Tutorials:

More...


Other Tutorials/FAQs:

More...


Related Resources:

More...


Selected Jobs:

More...