This discussion is archived
11 Replies Latest reply: Jan 10, 2013 8:52 AM by 858164 RSS

Error: ORA-01722: invalid number

858164 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    >
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Can you please show an example with sample data and code ...


    thanks in advance
  • 8. Re: Error: ORA-01722: invalid number
    sb92075 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    thanks John

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points