7

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

kzmlbyrk
  • 583
  • 1
  • 4
  • 17
  • 4
    Why on earth are you storing a date in a varchar column? –  Oct 24 '16 at 17:27
  • Probably because he is using an Oracle flexfield attribute (which are defined as varchar2) and masking it with a Date format validation set. – alexherm May 18 '21 at 22:13

3 Answers3

16

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.

  • 2
    I whould have gave you more that one vote up if I could! Thanks a lot – benshabatnoam Jul 12 '17 at 18:26
  • I totally agree, been working in DB's for years, first gig with Oracle however, this was great being able to transform it at the script level instead of the ETL. – VLOOKUP Feb 02 '23 at 17:06
4

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.

DCookie
  • 42,630
  • 11
  • 83
  • 92
0

Go to Tools —> Preferences —> Database —-> NLS —> Date Format and change date to DD/MM/YYYY .

Oracle SQL Developer Version 18.4.0.376 Build 376.1900

Ashish S
  • 1