Solved

Date Function

Posted on 2003-11-08
3
6,298 Views
Last Modified: 2008-03-10
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?
0
Comment
Question by:sindhuanand
3 Comments
 
LVL 15

Expert Comment

by:andrewst
ID: 9709412
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
 
LVL 8

Accepted Solution

by:
gajender_99 earned 125 total points
ID: 9716161
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
 

Expert Comment

by:rajarshidg
ID: 9720392
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

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to recover a database from a user managed backup

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question