Oracle to_date, from mm/dd/yyyy to dd-mm-yyyy

Question

I have all dates inserted into table as varchar2(10) and formatted as 'mm/dd/yyyy'. What I need is the following format 'mm-dd-yyyy' and date data type. My implementation without PLSQL would be:

select day||'-'||month||'-'||year as formatted_date from
(select 
extract( day from (select to_date('1/21/2000','mm/dd/yyyy')  from dual)) as day, 
to_number(extract( month from (select to_date('1/21/2000','mm/dd/yyyy')  from dual)),09) as month, 
extract( year from (select to_date('1/21/2000','mm/dd/yyyy')  from dual)) as year 
from dual);

Result is: 21-1-2000 not 21-01-2000 as expected.
When adding additional to_date(,) as:

to_date(day||'-'||month||'-'||year,'DD-MM-YYYY') as formatted_date

it doesn't even change day and month fields with eachother.

Answer 1

You don't need to muck about with extracting parts of the date. Just cast it to a date using to_date and the format in which its stored, then cast that date to a char in the format you want. Like this:

select to_char(to_date('1/10/2011','mm/dd/yyyy'),'mm-dd-yyyy') from dual