date-time conversion problem

[jfdabiri]

hi,
i have a table with the following columns and data:
start_date start_time
================= =================
04/10/07 00:00:00 01/01/01 17:32:05

finish_date finish_time
================= =================
05/31/07 00:00:00 01/01/01 10:50:02
i can get elapsed days with no problem.
is there anyway to calculate elapsed hours from these fields?
thanks.

 

[SantaMufasa]

jfdabiri,

Simply multiply the results by the appropriate factor. In the example, below, I calculate the difference between two days ago, yielding the difference in hours, then minutes, then seconds:

select (sysdate-(sysdate-2))*24 hours
      ,(sysdate-(sysdate-2))*24*60 mins
      ,(sysdate-(sysdate-2))*24*60*60 seconds
from dual;

HOURS       MINS    SECONDS
----- ---------- ----------
   48       2880     172800

Let us know if you have additional questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[jfdabiri]

thanks, Dave,
i don't have any problems with finding days or hours in days. but as you can see, the date and time are stored in different columns. so i have to find a way to "combine" these (date+time) to make a valid date-time string.

 

[SantaMufasa]

Sorry, jf...I didn't quite catch that.

What are the datatypes of those four columns...DATE or VARCHAR2? (And why did the database designers used such a convoluted, extraneous method to store your data?)

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[jfdabiri]

I inherited this data as is. both are defined as type "date"
it's an oracle database table.

 

[SantaMufasa]

Here, then, is code that should do what you want:

select (to_date(to_char(finish_date,'dd-mon-yyyy')
      ||to_char(finish_time,' hh24:mi:ss')
      ,'dd-mon-yyyy hh24:mi:ss')
        -
        to_date(to_char(start_date,'dd-mon-yyyy')
      ||to_char(start_time,' hh24:mi:ss')
      ,'dd-mon-yyyy hh24:mi:ss'))
      *24 elapsed_hours
  from jf;

ELAPSED_HOURS
-------------
   1217.29917

Let us know if this is satisfactory.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[jimirvine]

Hi,
Are you trying to calculate total number of hours between thos two dates, or the number of hours remaining after you have calculated the number of days (i.e. a calculation to show days, hours minutes and seconds elapsed)
If it is the former, then as per usual, Dave has provided a great answer for you. If it is the latter, the following example may help you on your way:

with 
t1 as (select to_date('04/10/07 00:00:00','mm/dd/yy hh24:mi:ss') start_date,           
              to_date('01/01/01 17:32:05','mm/dd/yy hh24:mi:ss') start_time,
              to_date('05/10/07 00:00:00','mm/dd/yy hh24:mi:ss') finish_date,
              to_date('01/01/01 10:50:02','mm/dd/yy hh24:mi:ss') finish_time
              FROM dual)
,
t as (select to_timestamp(to_char(finish_date,'dd-mon-yyyy')||to_char(finish_time,'hh24:mi:ss'),'dd-mon-yyyy hh24:mi:ss') finish
            ,to_timestamp(to_char(start_date,'dd-mon-yyyy')||to_char(start_time,'hh24:mi:ss'),'dd-mon-yyyy hh24:mi:ss') strt
            ,(to_timestamp(to_char(finish_date,'dd-mon-yyyy')||to_char(finish_time,'hh24:mi:ss'),'dd-mon-yyyy hh24:mi:ss')
                      -
              to_timestamp(to_char(start_date,'dd-mon-yyyy')||to_char(start_time,'hh24:mi:ss'),'dd-mon-yyyy hh24:mi:ss')) elapsed
      from t1)
select strt, finish, extract(hour from elapsed) from t;

By converting the concatenated date values into timestamps you are able to use the extract function to get the required 'components'
I also have to back up Dave's comment about the design. I understand that it is an inherited system, but the current design is going to continue to cause you problems unless and until you fix it.