-
date help
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
Last edited by ali560045; 07-01-2008 at 06:31 AM.
-
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
-
thanks this is working .
but to be presise i want in days-hour-min-sec format
for e.g.
143 days-01hour-23min-34sec
-------------------------------------------------------
-
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........
Last edited by ali560045; 07-01-2008 at 06:55 AM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|