oracle convert DD-MON-YY to DD/MM/YYYY

Question

I am trying to convert the format of a varchar2 column from 'DD-MON-YY' to 'DD/MM/YYYY'.

In example: from '01-JAN-16' to '01/01/2016'

In case you can ask or it may help:

• 'MON' part is in English however my current NLS settings are in Turkish.
• All the years are after 2000.

How can I do this? Thanks in advance..

Answer 1

If you don't provide the NLS_DATE_LANGUAGE parameter, your own session's parameter will be used.

You can override that like so:

select TO_CHAR(TO_DATE('01-JAN-16','DD-MON-YY', 'NLS_DATE_LANGUAGE = English'),
           'DD/MM/YYYY') from dual;

This will affect only this query, nothing else. If you need to work with many dates like this,

ALTER SESSION SET NLS_DATE_LANGUAGE='ENGLISH'

- then you can change it back later, or it will reset to Turkish when this session ends and you start another session.

If you need this change to be made (almost) permanent, put it in your settings in SQL Developer or Toad, or the login.sql for SQL*Plus.

Answer 2

Try this:

TO_CHAR(TO_DATE('01-JAN-16','DD-MON-YY'),'DD/MM/YYYY')

Your data must be clean - everything must conform to the original format or you'll encounter errors on the TO_DATE conversion.