Solved

Date Function

Posted on 2003-11-08
3
6,299 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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
join actual table rows based on the column 25 42
Error in creating a view. 8 50
SQL query to select row with MAX date 7 64
Convert summed columns to Rows 6 19
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to recover a database from a user managed backup

738 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