Solved

Date Function

Posted on 2003-11-08
3
6,300 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
[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
  • Learn & ask questions
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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

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 …
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to take different types of Oracle backups using RMAN.

729 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