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
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';