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

datetime, oracle, sql

Solution

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", ...)

Problem

Original source