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?
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).
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
BORNcolumn really a string (e.g.varchar2(9))? Or is it actually already adate, and your client is just currently showing it in that format via your session NLS_DATE_FORMAT setting? If it is data typedatethen you just need to convert that to a string for display, e.g.to_char(born, 'DD/MM/YYYY').