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

oracle date varchar2
kzmlbyrk picture kzmlbyrk · Oct 24, 2016 · Viewed 71.8k times · Source

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

mathguy picture mathguy · Oct 24, 2016

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.

Related questions

Updating a date in Oracle SQL table

I am trying to update a date in a SQL table. I am using Peoplesoft Oracle. When I run this query: Select ASOFDATE from PASOFDATE; I get 4/16/2012 I tried running this query UPDATE PASOFDATE SET ASOFDATE = '11/21/2012'; but it …

Read More
How to get the week day name from a date?

Given 03/09/1982 how can we say it is which week day. In this case it will be Tue. Is it possible to get in a single query?

Read More
Check if current date is between two dates Oracle SQL

I would like to select 1 if current date falls between 2 dates through Oracle SQL. I wrote an SQL after reading through other questions. https://stackoverflow.com/questions/2369222/oracle-date-between-query https://stackoverflow.com/questions/2399753/select-from-table-by-knowing-only-date-without-time-oracle But it returned only null. sysdate is …

Read More