SQL troubles

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 Received on Wed Aug 17 1994 - 04:14:03 CEST