11 Replies Latest reply: Jan 10, 2013 10:52 AM by 858164 RSS

    Error: ORA-01722: invalid number

    858164
      Hi

      Can some please tell me what i am doing wrong when i try to get the difference ?

      Since the format is same i use this query

      select b1- b2 from TMP_1;
      Error: ORA-01722: invalid number
      --create table         
          CREATE TABLE TMP_1
      (
        B1  VARCHAR2(8 BYTE),
        B2  VARCHAR2(8 BYTE)
      ) 
      /
      
      --insert 
      
      INSERT INTO TMP_1 (B1, B2)
           VALUES ('00:02:54', '00:02:10');
      
      INSERT INTO TMP_1 (B1, B2)
           VALUES ('00:05:47', '00:03:56');
      
      INSERT INTO TMP_1 (B1, B2)
           VALUES ('00:12:49', '00:02:37');
      
      INSERT INTO TMP_1 (B1, B2)
           VALUES ('00:49:33', '00:02:09');
      
      INSERT INTO TMP_1 (B1, B2)
           VALUES ('00:06:34', '00:02:39');
      
      INSERT INTO TMP_1 (B1, B2)
           VALUES ('00:02:45', '00:01:41');
      
      INSERT INTO TMP_1 (B1, B2)
           VALUES ('00:01:22', '00:00:22');
      
      COMMIT;
      objective:

      Need to get the difference in the same format as the
       columns example: 00:02:54 
        • 1. Re: Error: ORA-01722: invalid number
          rp0428
          >
          Can some please tell me what i am doing wrong when i try to get the difference ?
          >
          You can't subtract two strings like that.

          You will need to convert them to intervals or dates and then subtract them and format the result.

          Here is an example that computes the interval for you
          with q as (select '00:02:54' t1, '00:02:10' t2 from dual),
           q2 as (select to_date('01/01/2013 ' || t1, 'mm/dd/yyyy hh24:mi:ss') d1, '      ',
                 to_date('01/01/2013 ' || t2, 'mm/dd/yyyy hh24:mi:ss') d2 from q)
          select d1, d2, numtodsinterval(d1 - d2, 'DAY') int from q2
          
          D1     D2                                           INT
          1/1/2013 12:02:54 AM     1/1/2013 12:02:10 AM       +00 00:00:44.000000
          The interval could have days, hours, minutes, seconds and fractions of a second.
          • 2. Re: Error: ORA-01722: invalid number
            sb92075
            855161 wrote:
            Hi

            Can some please tell me what i am doing wrong when i try to get the difference ?

            Since the format is same i use this query

            select b1- b2 from TMP_1;
            Error: ORA-01722: invalid number
            --create table         
            CREATE TABLE TMP_1
            (
            B1  VARCHAR2(8 BYTE),
            B2  VARCHAR2(8 BYTE)
            ) 
            /
            
            --insert 
            
            INSERT INTO TMP_1 (B1, B2)
            VALUES ('00:02:54', '00:02:10');
            
            INSERT INTO TMP_1 (B1, B2)
            VALUES ('00:05:47', '00:03:56');
            
            INSERT INTO TMP_1 (B1, B2)
            VALUES ('00:12:49', '00:02:37');
            
            INSERT INTO TMP_1 (B1, B2)
            VALUES ('00:49:33', '00:02:09');
            
            INSERT INTO TMP_1 (B1, B2)
            VALUES ('00:06:34', '00:02:39');
            
            INSERT INTO TMP_1 (B1, B2)
            VALUES ('00:02:45', '00:01:41');
            
            INSERT INTO TMP_1 (B1, B2)
            VALUES ('00:01:22', '00:00:22');
            
            COMMIT;
            objective:

            Need to get the difference in the same format as the
             columns example: 00:02:54 
            what is the result when you subtract 'IDIOT' from 'NONSENSE';
            which makes as much sense as what you proposed to do.
            • 3. Re: Error: ORA-01722: invalid number
              858164
              HI

              i understand that you can't substract varchar values for date to get the difference ...

              i was may be not clear to ask the question correctly ... so sorry about that


              the problem on hand is that i have two columns one with sysdate (date format) and other column is number 234 (number format)

              Now it may seem simple for you but i am not sure to how to format the numbers columns in a way that i can that i can get the difference.
              for example :
              
              table:1
              
              column1                               column2
              2013/01/09 3:25:11 PM            20
              
              the output would be : 2013/01/09 3:05:11 PM 
              Edited by: 855161 on Jan 9, 2013 12:29 PM
              • 4. Re: Error: ORA-01722: invalid number
                sb92075
                855161 wrote:
                HI

                listen i understand well that you can't substract varchar for date to get the difference ...

                but you are right i was not clear to ask the question correctly ...


                the problem on hand is that i have two columns one with sysdate (date format) and other column is number 234 (number format)
                What is unit of measure for this number; microfortnights, centuries, full moons?

                provide sample input values & expect/desired results & explain how to transform inputs into output.
                • 5. Re: Error: ORA-01722: invalid number
                  858164
                  Hi not sure what you mean ?
                  What is unit of measure for this number?
                  objective  have column b3 as follow: b3 =  (B1- b2)
                  example : 
                  
                  b1= 12/03/2012 20:13:33
                  b2=  00:02:10
                  b3= 12/03/2012 20:11:23                                    
                  CREATE TABLE TMP_1
                  (
                    B1  DATE,
                    B2  VARCHAR2(8 BYTE)
                  )
                   
                   
                   Insert into TMP_1
                     (B1, B2)
                   Values
                     (TO_DATE('12/03/2012 20:13:33', 'MM/DD/YYYY HH24:MI:SS'), '00:02:10');
                  Insert into TMP_1
                     (B1, B2)
                   Values
                     (TO_DATE('12/03/2012 18:24:24', 'MM/DD/YYYY HH24:MI:SS'), '00:03:56');
                  Insert into TMP_1
                     (B1, B2)
                   Values
                     (TO_DATE('12/03/2012 21:48:33', 'MM/DD/YYYY HH24:MI:SS'), '00:02:37');
                  Insert into TMP_1
                     (B1, B2)
                   Values
                     (TO_DATE('12/03/2012 15:50:09', 'MM/DD/YYYY HH24:MI:SS'), '00:02:09');
                  Insert into TMP_1
                     (B1, B2)
                   Values
                     (TO_DATE('12/03/2012 14:06:24', 'MM/DD/YYYY HH24:MI:SS'), '00:02:39');
                  Insert into TMP_1
                     (B1, B2)
                   Values
                     (TO_DATE('12/03/2012 21:47:15', 'MM/DD/YYYY HH24:MI:SS'), '00:01:41');
                  COMMIT;
                  • 6. Re: Error: ORA-01722: invalid number
                    sb92075
                    the problem on hand is that i have two columns one with sysdate (date format)
                    and other column is number 234 (number format)
                    the "number" seems to have morphed into a VARCHAR2.
                    "number" does not contain colon character.

                    You need to transform the string into an INTERVAL prior to using it in an arithmetic operation.
                    • 7. Re: Error: ORA-01722: invalid number
                      858164
                      Can you please show an example with sample data and code ...


                      thanks in advance
                      • 8. Re: Error: ORA-01722: invalid number
                        sb92075
                        855161 wrote:
                        Can you please show an example with sample data and code ...


                        thanks in advance
                        sure.

                        stand by

                        Handle:     855161
                        Status Level:     Newbie
                        Registered:     Apr 27, 2011
                        Total Posts:     72
                        Total Questions:     17 (12 unresolved)
                        • 9. Re: Error: ORA-01722: invalid number
                          858164
                          if you don't want to help that's fine .... but please don't waste your time and be sarcastic ... you think you are special cause you have experience ... you also started from zero

                          so be considerate to others don't be hard on people who are struggling ....


                          thanks ...
                          • 10. Re: Error: ORA-01722: invalid number
                            John Spencer
                            Thanks for supplying sample data it is very helpful. Here is one way:
                            SQL> select b1, b2,
                              2         b1 - (to_date(to_char(sysdate, 'dd-mon-yyyy')||' '||b2,'dd-mon-yyyy hh24:mi:ss') - trunc(sysdate)) b3
                              3  from tmp_1;
                            
                            B1                   B2       B3
                            -------------------- -------- --------------------
                            03-dec-2012 20:13:33 00:02:10 03-dec-2012 20:11:23
                            03-dec-2012 18:24:24 00:03:56 03-dec-2012 18:20:28
                            03-dec-2012 21:48:33 00:02:37 03-dec-2012 21:45:56
                            03-dec-2012 15:50:09 00:02:09 03-dec-2012 15:48:00
                            03-dec-2012 14:06:24 00:02:39 03-dec-2012 14:03:45
                            03-dec-2012 21:47:15 00:01:41 03-dec-2012 21:45:34
                            John
                            • 11. Re: Error: ORA-01722: invalid number
                              858164
                              thanks John