CODEWITHSUNDEEP
oracle-databasedatevarchar2
kzmlbyrk
kzmlbyrk

Reputation: 593

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

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:

How can I do this? Thanks in advance..

Upvotes: 7

Views: 103070

Answers (3)

Ashish S
Ashish S

Reputation: 1

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

Upvotes: 0

user5683823
user5683823

Reputation:

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.

Upvotes: 16

DCookie
DCookie

Reputation: 43523

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.

Upvotes: 4

Related Questions