Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

Date Function

Posted on 2003-11-08
Medium Priority
6,302 Views
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
Question by:sindhuanand
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 15

Expert Comment

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

gajender_99 earned 500 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

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

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
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.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Suggested Courses
Course of the Month9 days, 18 hours left to enroll