| date diff [message #10358] |
Sat, 17 January 2004 04:09  |
resy
Messages: 86
Registered: December 2003
|
Member |
|
|
hi,
Is there any single Query to find the difference of two dates in Years,Months,Weeks,Days,Hours,Minutes & Seconds .
thanx.
|
|
|
|
|
|
| Re: date diff [message #10362 is a reply to message #10358] |
Sat, 17 January 2004 10:18  |
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
|
Senior Member |
|
|
I thought there would be a straighforward solution using TIMESTAMP and INTERVAL datatypes in 9i, but these still seem to have some limitations, and the nearest I can get in one shot is an INTERVAL DAY(5) TO SECOND(0) value such as
+00725 19:02:43
Using plain old date arithmetic:
desc test_dates
Name Null? Type
----------------------- -------- ----------------
D1 DATE
D2 DATE
SELECT TO_CHAR(d1,'DD-MON-YYYY HH24:MI:SS') AS d1
, TO_CHAR(d2,'DD-MON-YYYY HH24:MI:SS') AS d2
, TRUNC(MONTHS_BETWEEN(d2, d1)/12) AS years
, MOD(TRUNC(MONTHS_BETWEEN(d2, d1)),12) AS months
, TRUNC(ADD_MONTHS(d2, TRUNC(MONTHS_BETWEEN(d2, d1)) * -1) -d1) AS days
, TO_CHAR(TRUNC(SYSDATE) + ABS(MOD(d2 - d1, 1)), 'HH24:MI:SS') AS time
FROM test_dates
/
D1 D2 YEARS MONTHS DAYS TIME
-------------------- -------------------- ---------- ---------- ---------- --------
14-JUL-1789 00:00:00 17-JAN-2004 19:01:53 214 6 3 19:01:53
06-SEP-1998 00:00:00 31-AUG-2000 19:02:43 1 11 24 19:02:43
17-JAN-2004 19:03:30 17-JAN-2004 08:41:17 0 0 0 10:22:13 |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
