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

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

Solution 2

You have to convert your string to date and than convert to char with expected format

select TO_CHAR(TO_DATE(datecol, 'MM/DD/YYYY'),'YYYY-MM-DD')
from your_table

Sql Fiddle Demo

Comments

[Robert]

OP's column with date is varchar2 not timestamp

[MontyPython]

I know we shouldn't but what do I tell those guys who have created the data warehouse and are not freaking us out with 15 different formats of dates in a single columns. What do I tell them?

[a_horse_with_no_name]

@MontyPython: tell them that this is a very bad way to store a date. This should be considered a critical bug which needs to be fixed immediately. You may refer to this thread if you want to :)

[MontyPython]

@a_horse_with_no_name - I am already aware of that but they don't seem to care. It's the data analysis team that suffers from such stupid database schemes.