oracle sql:how to convert string datetime to specified format-CodePudding

I've been searching around for a while now, but I can't seem to find the answer to this small problem.

how to convert string 06-JUL-89 to datetime 06/07/1989? I've tried with code like the following:

TO_CHAR(TO_DATE(BORN,'DD-MON-YY'),'DD/MM/YYYY')

however, the result shows wrong: to be 06/07/2089? how do i solve the problem?

CodePudding user response：

With RR format model.

SQL> select to_char(to_date('06-jul-89', 'dd-mon-rr'), 'dd/mm/yyyy') result from dual;
                                                 --
RESULT                                          here
----------
06/07/1989

SQL>

By the way, it looks as if you store date values as strings. If so, don't do that - switch to DATE datatype (as soon as possible).

CodePudding user response：

It seem that you have the problem of year 2k :

TO_DATE('06-jul-89', 'dd-mon-yy') => 06/07/2089
You must use TO_DATE('06-jul-89', 'dd-mon-rr') => 06/07/1989