Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Location
    Sochaux (France)
    Posts
    55

    Unhappy Unanswered: ORA-01858 a non-numeric character was found where a numeric was expected

    And i don't understan why.

    I've got 2 table whith data type DATE.
    When i run this query, it works :
    PHP Code:
    SELECT FLOOR(MONTHS_BETWEEN(SYSDATEDQAQT24.DATE_ENTREE_GARANTIE)) 
    FROM   DQAQT24DQAQT10
    WHERE  DQAQT24
    .VIN DQAQT10.VIN
    I've got months between sysdate and my field.
    But when i introduce DECODE, it makes an ORA 01858 Error
    PHP Code:
    SELECT FLOOR(MONTHS_BETWEEN(SYSDATE
                       
    DECODE(TO_DATE(DQAQT24.DATE_ENTREE_GARANTIE'YYYYMMDD'), 
                                      
    TO_DATE('19000101''YYYYMMDD'), SYSDATE
                                    
    DQAQT24.DATE_ENTREE_GARANTIE))) 
    FROM   DQAQT24DQAQT10
    WHERE  DQAQT24
    .VIN DQAQT10.VIN;

    SELECT DECODE(TO_DATE(DQAQT24.DATE_ENTREE_GARANTIE'YYYYMMDD'), 
                                       
    TO_DATE('19000101''YYYYMMDD'), 0,
                                       
    FLOOR(MONTHS_BETWEEN(SYSDATEDQAQT24.DATE_ENTREE_GARANTIE))) 
    FROM   DQAQT24DQAQT10
    WHERE  DQAQT24
    .VIN DQAQT10.VIN
    Thanx for your help
    Last edited by venderic; 04-14-04 at 08:48.

  2. #2
    Join Date
    Jan 2004
    Location
    Sochaux (France)
    Posts
    55
    Solved...

    Sorry

  3. #3
    Join Date
    Feb 2004
    Location
    Jordan
    Posts
    137
    how did u solve it ?????

    Originally posted by venderic
    Solved...

    Sorry

  4. #4
    Join Date
    Jan 2004
    Location
    Sochaux (France)
    Posts
    55
    Originally posted by giggs11
    how did u solve it ?????
    I was using TO_DATE instead of TO_CHAR.
    Conversion between DATE, NUMBER and CHAR are not really intuitive....

    PHP Code:
    SELECT FLOOR(MONTHS_BETWEEN(SYSDATE
                       
    DECODE(TO_CHAR(DQAQT24.DATE_ENTREE_GARANTIE'YYYYMMDD'), 
                                      
    '19000101'SYSDATE
                                    
    DQAQT24.DATE_ENTREE_GARANTIE))) 
    FROM   DQAQT24DQAQT10
    WHERE  DQAQT24
    .VIN DQAQT10.VIN;

    SELECT DECODE(TO_CHAR(DQAQT24.DATE_ENTREE_GARANTIE'YYYYMMDD'), 
                                       
    '19000101'0,
                                       
    FLOOR(MONTHS_BETWEEN(SYSDATEDQAQT24.DATE_ENTREE_GARANTIE))) 
    FROM   DQAQT24DQAQT10
    WHERE  DQAQT24
    .VIN DQAQT10.VIN

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •