Skip to content
Advertisement

format date in oracle SQL

I was trying to learn how to format date in oracle pl oracle, when I ran below query its returns error

SELECT TO_DATE('01-JAN-00', 'YYYY-DD-MM') FROM dual;

the error message is

ORA-01858: a non-numeric character was found where a numeric was expected
01858. 00000 -  "a non-numeric character was found where a numeric was expected"
*Cause:    The input data to be converted using a date format model was
           incorrect.  The input data did not contain a number where a number was
           required by the format model.
*Action:   Fix the input data or the date format model to make sure the
           elements match in number and type.  Then retry the operation.

Advertisement

Answer

for my scenario I had to use to_char which perfectly solve the formatting issue.

SELECT TO_CHAR('01-JAN-00', 'yyyy-DD-MM') FROM dual;
User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement