• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 446
  • Last Modified:

oracle date- finding elapsed time

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
anumoses
Asked:
anumoses
  • 5
  • 3
1 Solution
 
sdstuberCommented:
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
 
sdstuberCommented:
that will actually support up to 999 hours
0
 
anumosesAuthor Commented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
anumosesAuthor Commented:
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
 
anumosesAuthor Commented:
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
 
anumosesAuthor Commented:
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
 
sdstuberCommented:
####: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
 
anumosesAuthor Commented:
thanks
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now