Incorrect Date format error

Question

I am getting an error:

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

while executing this code:

SELECT 
    user_id,
    user_name,
    employee_id,
    start_date,
    end_date,
    last_update_date,
    MAX (start_date) OVER (PARTITION BY f.employee_id) AS max_start_date,
    MAX (last_update_date) OVER (PARTITION BY f.employee_id) AS max_last_update_date
FROM 
    refadm.cg1_fnd_user f
WHERE 
    TO_DATE (SYSDATE, 'DD/MON/YY  HH:MI:SSAM') <=
          COALESCE (TO_DATE (f.end_date, 'yyyy-MM-dd'), SYSDATE + 1)

Please read this https://stackoverflow.com/help/someone-answers — XING, Jan 11 '18 at 07:30

Gordon Linoff · Answer 1 · 2017-07-21T12:23:45.510

3

Don't convert sysdate to a date -- it is already a date:

WHERE SYSDATE <= TO_DATE(f.end_date, 'yyyy-MM-dd') OR
      f.end_date IS NULL

You may still get the problem, depending on what f.end_date looks like. But the logic is at least simpler.

If f.end_date is only sometimes in yyyy-MM-dd format, then you can use regular expressions or a user-defined function to handle the invalid formats.

EDIT:

If end_date is in a different format, then use the correct format:

WHERE SYSDATE <= TO_DATE(f.end_date, 'DD/MM/YYYY') OR
      f.end_date IS NULL

edited Jul 21 '17 at 12:23

answered Jul 21 '17 at 10:55

Gordon Linoff

1,242,037
58
646
786

f.end_date has date value in the format - 3/26/2013 – Roly Singh Jul 21 '17 at 11:00
Then you must use `TO_DATE(f.end_date, 'dd/MM/yyyy')` - in case it is really a **string**, i.e. `VARCAHR2` data type. – Wernfried Domscheit Jul 21 '17 at 12:18

score 0 · Answer 2 · answered Jul 21 '17 at 12:45

0

If your string has format '3/26/2013' - you wrote a wrong format in to_date function. It should be TO_DATE (f.end_date,'MM-DD-YYYY') The whole sentence can look like

WHERE SYSDATE <= TO_DATE(f.end_date, 'MM-DD-YYYY') OR
      f.end_date IS NULL

answered Jul 21 '17 at 12:45

Anastasia S.

1
2

Incorrect Date format error

2 Answers2