problem with using to_date in oracle query

Question

i simply wanna change a string to a date format using to_date SELECT TO_DATE('20-APR-20 09.50.06 AM' , 'DD-MOM-YY HH24:MI:SS AM') FROM DUAL;

and also i want to change to 24 format

when i run this i get the ORA-01821: date format not recognized error .

In addition to the other comments -- always, always, always use 4-digit years, not 2-digit. Does the term 'Y2k bug' ring any bells? — EdStevens
– EdStevens, Commented Jun 18, 2020 at 14:12

Gordon Linoff · Accepted Answer · 2020-06-18 14:09:16Z

2

The correct format for converting your string to a date is:

SELECT TO_DATE('20-APR-20 09.50.06 AM' , 'DD-MON-YY HH.MI.SS AM')
FROM DUAL;

If you want it as a string, then you can use TO_CHAR() after converting to a date. That said, I recommend keeping the value as a date.

answered Jun 18, 2020 at 14:09

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

monika · Accepted Answer · 2020-06-18 20:35:35Z

1

The correct format is

SELECT TO_CHAR(TO_DATE('20/APR/20 09.50.06 AM' , 'DD-MON-YY HH:MI:SS AM'),'DD-MON-YY HH:MI:SS AM') FROM DUAL;

answered Jun 18, 2020 at 20:35

monika

678 bronze badges