CODEWITHSUNDEEP
sqloracle-databasedatetime
Niken Amelia
Niken Amelia

Reputation: 37

oracle sql:how to convert string datetime to specified format

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?

Upvotes: 0

Views: 315

Answers (2)

HMFouad
HMFouad

Reputation: 1

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

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 143163

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).

Upvotes: 1

Related Questions