Categories:
.NET (357)
C (330)
C++ (183)
CSS (84)
DBA (2)
General (7)
HTML (4)
Java (574)
JavaScript (106)
JSP (66)
Oracle (114)
Perl (46)
Perl (1)
PHP (1)
PL/SQL (1)
RSS (51)
Software QA (13)
SQL Server (1)
Windows (1)
XHTML (173)
Other Resources:
How To Calculate Date and Time Differences
How To Calculate Date and Time Differences? - Oracle DBA FAQ - Understanding SQL Basics
✍: FYIcenter.com
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
2007-04-23, 6509👍, 0💬
Popular Posts:
How To View All Columns in an Existing Table? - Oracle DBA FAQ - Managing Oracle Database Tables If ...
.NET INTERVIEW QUESTIONS - What is the difference between System exceptions and Application exceptio...
How is the MVC design pattern used in Struts framework? In the MVS design pattern, there 3 component...
How To Join a List of Keys with a List of Values into an Array? - PHP Script Tips - PHP Built-in Fun...
What are unadjusted function points and how is it calculated? Unadjusted function points = ILF + EIF...