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

Question

The date column that I have is in varchar2 and I want to convert those values in YYYY-MM-DD

DATE
7/26/2013
7/29/2013
8/1/2013 
8/4/2013 
7/28/2013
7/31/2013
8/3/2013 
7/30/2013
8/5/2013 
7/25/2013
8/2/2013 
8/6/2013 
7/27/2013

Answer 1

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