why can not I format a sql date?-CodePudding

In the following code, I can not convert the date to the given format. why?

select sysdate,to_char('09/21/1990','ddth "of" mm yyyy') as formated
from emp;

error message:

ORA-01722: invalid number

CodePudding user response：

The to_char function is to convert a DATE type to a string. Your date parameter is already a string. If you really are executing the query with a string literal, you would have to convert it to a DATE type first, then to the string format you need, like this

select sysdate,
       to_char(to_date('09/21/1990','MM/DD/YYYY'),'ddth "of" mm yyyy')  as formated
from emp;