Re: SQL troubles

Laura Worthington (ljw_at_evolving.com) wrote:
:
:
: I have a select statement that almost does what I want, but not quite....
:
: select count(column1)/(to_char(column2, 'HH24') - to_char(column3, 'HH24'))
: from my_table;
:
:
: column1, column2, and column3 are all columns of the type date and they
: get put into the database using the format 'YYYYMMDDHH24MISS'.
:
: The problem is, if column2='19940816020000' and column3='19940816043000'
: then effectively I am taking the difference between 4:30 and 2:00 but because
: of my 'HH24's, the difference is only 2 hours instead of 2.5. If I use
: formats of 'HH24MI' then the difference will return 230 and I will end up
: with some_number/230 when I really want some_number/2.5 hours.
:
: I have tried putting in a to_char to convert the difference into HH24MI
: but get the error ORA-01481: invalid number format model
:
: Any ideas?
:
: laura
:
Try this:

select count(column1)/((column3 - column2) * 24) from my_table;

You can subtract dates, and it will return a decimal part of a day. Then multiply by 24 to get the hours.

Phil

-- 
  +---------------------------------------------------------------------+
  |  Phillip Huber   Analyst/Programmer   Michigan Technological Univ.  |
  |  Internet: phil_at_mtu.edu    Phone:(906)487-2223    Fax:(906)487-2521 |
  +---------------------------------------------------------------------+