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

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.

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

I suggest you use TO_CHAR() when converting to string. In order to do that, you need to build a date first.

SELECT TO_CHAR(TO_DATE(DAY||'-'||MONTH||'-'||YEAR, 'dd-mm-yyyy'), 'dd-mm-yyyy') 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
    );

select to_char(to_date('1/21/2000','mm/dd/yyyy'),'dd-mm-yyyy') from dual