date help

[ali560045]

i have a table called std_detail.I m geting the max and min date from the table and then finding the difference b/w dates:

ENAME TIME_VALUE
naveed 09/10/2008 08:30:50
amit 10/30/2008 00:00:00
raja 11/17/2008 03:30:50
fasi 10/25/2008 01:30:50
yuvi 06/27/2008 17:55:06
-----------------------------------------------

select
(select to_char(max(time_value),'dd/mm/RR HH:MI:SS') from std_detail) -
(select to_char(min(time_value),'dd/mm/RR HH:MI:SS') from std_detail) "Days diff"
from dual

-----------------------------------------------------------------

the above command not working showing ORA-01722.



--------------------------------------------------------------------------
if i use this sql :

select
(select to_date(max(time_value),'dd/mm/yyyy hh:mi:ss') from std_detail) -
(select to_date(min(time_value),'dd/mm/yyyy hh:mi:ss') from std_detail) "Days diff"
from DUAL


above sql gives only the date diff but not the hour min diff alongside date

[citldba]

How about :

select to_char(max(time_value),'dd/mm/RR HH:MI:SS'),
to_char(min(time_value),'dd/mm/RR HH:MI:SS'),
max(time_value) - min(time_value)
from std_detail

[ali560045]

thanks this is working .

but to be presise i want in days-hour-min-sec format

for e.g.

143 days-01hour-23min-34sec
-------------------------------------------------------

[ali560045]

i got the below sql

select ( max(time_value) - min(time_value))
DAY(9) TO SECOND FROM std_detail;

output: +142 09:35:44.000000


----------------------------------------------------

min(time_value)---------> 27/06/2008 17:55:06

max(time_value)---------> 17/11/2008 03:30:50

result ( max(time_value) - min(time_value)----> 142 09:35:44.000000
------------------------------------------------------------------------

got the answer now. thanks ever1 for ur help........