oracle-database

Date SQL in oracle throwing error - ORA-01858 Error message - ORA-01858: a non-numeric character was found where a numeric was expected 01858


SELECT TO_DATE(pa.actual_exp_date, 'YYYYMMDD') - TO_DATE('20230531', 'YYYYMMDD')
         AS out_days
FROM   pre_agrees pa
       JOIN pass p ON pa.pre_id = p.pass_id
WHERE  pa.prepaid_status = 'A'
AND    to_char(pa.actual_exp_date, 'YYYYMMDD') >= '20230531'
AND    actual_exp_date IS NOT NULL
AND    to_char(p.effective_date, 'YYYYMMDD') <= '20230531';

Date SQL in oracle throwing error:

ORA-01858 Error message - ORA-01858: a non-numeric character was found where a numeric was expected 01858


Solution

  • Never use TO_DATE on a value that is already a DATE data-type.

    If pre_agrees.actual_exp_date and pass.effective_date are both DATE columns then:

    SELECT TRUNC(pa.actual_exp_date) - DATE '2023-05-31' AS out_days
    FROM   pre_agrees pa
           JOIN pass p ON pa.pre_id = p.pass_id
    WHERE  pa.prepaid_status  =  'A'
    AND    pa.actual_exp_date >= DATE '2023-05-31'
    AND    p.effective_date   <  DATE '2023-05-31' + 1;
    

    Note: The actual_exp_date IS NOT NULL filter is unnecessary as pa.actual_exp_date >= DATE '2023-05-31' will only be true if the column is NOT NULL.


    If pre_agrees.actual_exp_date and pass.effective_date are both VARCHAR2 columns then (apart from it being bad practice to store dates as strings):

    SELECT TO_DATE(pa.actual_exp_date, 'YYYYMMDD') - DATE '2023-05-31' AS out_days
    FROM   pre_agrees pa
           JOIN pass p ON pa.pre_id = p.pass_id
    WHERE  pa.prepaid_status = 'A'
    AND    pa.actual_exp_date >= '20230531'
    AND    p.effective_date <= '20230531';