Date Function

This sql statement retrieves
select to_date('29-OCT-03 11:31:06AM','DD-MON-RR HH:MI:SSAM'),
to_date('29-OCT-03 06:18:19PM','DD-MON-RR HH:MI:SSAM'),
ROUND((to_date('29-OCT-03 06:18:19PM','DD-MON-RR HH:MI:SSAM')-
to_date('29-OCT-03 11:31:06AM','DD-MON-RR HH:MI:SSAM')),2)*24 DUR
FROM DUAL


6.78 for the dur column.Im displaying it as hours.After the decimal i dnt want .78.It should be actually 60+18.That means the actual value should be 7.18(7hrs and 18 secs)
How can i get this?
sindhuanandAsked:
Who is Participating?
 
gajender_99Connect With a Mentor Commented:
hi try this

select  
trunc(dur)||round((dur - trunc(dur))*60)/100 as hr
from(select to_date('29-OCT-03 11:31:06AM','DD-MON-RR HH:MI:SSAM'),
to_date('29-OCT-03 06:18:19PM','DD-MON-RR HH:MI:SSAM'),
ROUND((to_date('29-OCT-03 06:18:19PM','DD-MON-RR HH:MI:SSAM')-
to_date('29-OCT-03 11:31:06AM','DD-MON-RR HH:MI:SSAM')),2)*24 DUR
FROM DUAL
)

because floor commands gives you the largest number in case of minus  and the leat in case of posituve

for i.e

select floor(-24.5), floor(24.5)
from dual
----------------------------------
the result is
   -25    24
0
 
andrewstCommented:
6.78 doesn't mean 6 hours and 78 minutes, it means 6.78 hours which is correct.  To convert to hours and minutes, you could do this:

h := FLOOR(dur);
m := (dur-FLOOR(dur))*60;

Or in SQL:

select FLOOR(dur) h, (dur-FLOOR(dur))*60 m
from
(
select to_date('29-OCT-03 11:31:06AM','DD-MON-RR HH:MI:SSAM'),
to_date('29-OCT-03 06:18:19PM','DD-MON-RR HH:MI:SSAM'),
ROUND((to_date('29-OCT-03 06:18:19PM','DD-MON-RR HH:MI:SSAM')-
to_date('29-OCT-03 11:31:06AM','DD-MON-RR HH:MI:SSAM')),2)*24 DUR
FROM DUAL
);
0
 
rajarshidgCommented:
1stly your SQL is returning 6.72 & not 6.78 for the dur column. Here .72 DOES NOT mean 60 + 12 but it means 72/100*60 = 43.2 minutes. Again here .2 means 2/100*60 = 1.2 seconds. So, the value 6.72 in dur column means 6 hrs 43 minutes and 1.2 seconds and not 7.18 (7hrs and 18 secs). So, I feel the answer you are gttting is quite right. You may try this also...

select to_date('29-OCT-03 11:31:06AM','DD-MON-RR HH:MI:SSAM'),
to_date('29-OCT-03 06:18:19PM','DD-MON-RR HH:MI:SSAM'),
floor(ROUND((to_date('29-OCT-03 06:18:19PM','DD-MON-RR HH:MI:SSAM')-
to_date('29-OCT-03 11:31:06AM','DD-MON-RR HH:MI:SSAM')),2)*24) || ' Hr ' ||
(ROUND((to_date('29-OCT-03 06:18:19PM','DD-MON-RR HH:MI:SSAM')-
to_date('29-OCT-03 11:31:06AM','DD-MON-RR HH:MI:SSAM')),2)*24 -
floor(ROUND((to_date('29-OCT-03 06:18:19PM','DD-MON-RR HH:MI:SSAM')-
to_date('29-OCT-03 11:31:06AM','DD-MON-RR HH:MI:SSAM')),2)*24))*60 || ' Min' "DUR"
FROM DUAL

This will give you in HRS & MINS.
0
All Courses

From novice to tech pro — start learning today.