SQL Troubles

From: Adrian Wilson <awilson_at_intermec.com>
Date: 17 Aug 94 15:11:48 GMT
Message-ID: <2722_at_intermec.UUCP>

> Laura _at_ljw_at_evolving.com on 17 Aug 1994 (titled: SQL Troubles) writes:
 

> 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

I created a table my_table, and inserted some dates,

COL1                 COL2                                                       
-------------------- --------------------                                       
01-JAN-1994 00:00:00 31-DEC-1994 23:59:59                                       
01-FEB-1994 00:00:00 03-FEB-1994 06:30:00                                       
16-AUG-1994 02:00:00 16-AUG-1994 04:30:00                                       

Using Oracle date arithmetic which return days and multiplying by 24 hours,

SQL> l
  1 select to_char( col1, 'DD-MON-YYY HH24:MI:SS' ) col1,

  2         to_char( col2, 'DD-MON-YYY HH24:MI:SS' ) col2,
  3         24*(col2 - col1)

  4* from my_table
SQL> /

COL1                 COL2                 24*(COL2-COL1)                        
-------------------- -------------------- --------------                        
01-JAN-994 00:00:00  31-DEC-994 23:59:59      8759.99972                        
01-FEB-994 00:00:00  03-FEB-994 06:30:00            54.5                        
16-AUG-994 02:00:00  16-AUG-994 04:30:00             2.5                        

Hope this answers your question.

Adrian Wilson  

    //      //  //    //  //////  ///////   //////    ///////   ////////
   //      //  ////  //  //      //    // //     //  //        //
  //      //  // // //  //      ///////  //     //  ////////  /////////
 //      //  //  ////  //      //    // //     //        //         //
/////// //  //    //  /////// //    //   //////    ///////   //////// 
              S O F T W A R E  S Y S T E M S , I N C.
                S e a t t l e, W a s h i n g t o n
                Tel:  ( 2  0  6 ) 2 3 6 - 5 8 4 7

    "In pursuit of Software Excellence & Competitive Advantage" Received on Wed Aug 17 1994 - 17:11:48 CEST