?
Solved

oracle date- finding elapsed time

Posted on 2012-03-19
8
Medium Priority
?
435 Views
Last Modified: 2012-03-26
http://www.experts-exchange.com/Database/Oracle/Q_27540184.html

Earlier answered question.

select to_char(floor((sysdate -bleed_end_time)*24),'fm09') || ':' || to_char(round(mod((sysdate -bleed_end_time)*24,1) * 60),'fm09')
from units@qlab  
 where unit_id = 'W039712003106'

by: sdstuberPosted on 2012-01-17 at 06:28:39ID: 37447220
----------------
Now my requirement is changed. Instead of 24 hours it has to be upto 120 hours.  Need help in this. Appreciate.
0
Comment
Question by:anumoses
[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
  • 5
  • 3
8 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 1000 total points
ID: 37739555
select to_char(floor((sysdate -bleed_end_time)*24),'fm009') || ':' || to_char(round(mod((sysdate -bleed_end_time)*24,1) * 60),'fm09')
from units@qlab  
 where unit_id = 'W039712003106'
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37739556
that will actually support up to 999 hours
0
 
LVL 6

Author Comment

by:anumoses
ID: 37739593
TO_CHAR(FLOOR((SYSDATE-BLEED_END_TIME)*24),'FM009')||':'||TO_CHAR(ROUND(MOD((SYSDATE-BLEED_END_TIME)*24,1)*60),'FM09')

####:20

I dont think this is right.
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 6

Author Comment

by:anumoses
ID: 37739619
select to_char(floor((sysdate -bleed_end_time)*24),'fm009') || ':'
|| to_char(round(mod((sysdate -bleed_end_time)*24,1) * 60),'fm09')
from units@qlab  
 where unit_id = 'W039712003902'

---------------
120:24

I think we are in the right direction.
Thanks,
0
 
LVL 6

Author Comment

by:anumoses
ID: 37739663
select to_char(floor((sysdate -bleed_end_time)*24),'fm009') || ':' || to_char(round(mod((sysdate -bleed_end_time)*24,1) * 60),'fm09')
from units@qlab  
 where unit_id = 'W039712003902'

120:33
 
select (sysdate - bleed_end_time)
from units@qlab  
where unit_id = 'W039712003902'  


5.02341435185185

      select to_char(trunc(sysdate)+ 5.02341435185185,'hh24:mi')
 from dual;

00:33

Here I need to get that 120:33
0
 
LVL 6

Author Comment

by:anumoses
ID: 37739764
I think I am good with the previous solution. Instead of converting further, I am diplaying the first value of 120:33 . Will make further tests and let you know. Thanks.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37740108
####:20  --- this means the result was 4 digits, i.e.  > 999 hours

if you need to display a results that large
change

this:   to_char(floor((sysdate -bleed_end_time)*24),'fm009')


to:   to_char(floor((sysdate -bleed_end_time)*24),'fm0009')

you can keep adding 0s to the format to expand as needed.



I'm not sure what you were trying to say in http:#a37739663
0
 
LVL 6

Author Closing Comment

by:anumoses
ID: 37768330
thanks
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
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 video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

771 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