How to Change the Date Format from Mm/Dd/Yyyy to Yyyy-Mm-Dd in Pl/Sql

Convert mm/dd/yyyy to yyyy-mm-dd in Oracle

You should never store dates in a VARCHAR column

So in order to display it differently now, you need to first convert the string to a date and then back to a string

If you are certain that all "dates" do have the right (and same) format, then the following should work:

select to_char(to_date(date, 'mm/dd/yyyy'), 'yyyy-mm-dd')
from the_table;

But I wouldn't be surprised if that gives you an error because one or more rows have a date which is formatted differently. Something which could not have happened had you defined the column to be of type DATE right from the beginning.

You should really, really consider changing that column to be a real DATE column.

Btw: DATE is a horrible name for such a column. It is a reserved word and it simply doesn't tell you anything about what is actually stored in that column (a "start date", an "end date", a "birth date", a "due date", ...)

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

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.

PL/SQL Oracle v9 - How to change Timestamp into Date mm/dd/YYYY

Dates (and timestamps) do not have a format - they are represented internally by 7 or 8 bytes for a date or 20 bytes for a timestamp.

If you want to format a date or a timestamp then you will have to convert it to a string either explicitly with TO_CHAR():

SELECT TO_CHAR( date_column, 'MM/DD/YYYY' )
FROM   your_table;

Or implicitly within whatever client program (i.e. you've tagged plsqldeveloper) you are using - in which case check the preferences within your IDE.

---

Related Topics