title: Oracle to_date, from mm/dd/yyyy to dd-mm-yyyy
body: <p>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:
  <br>
  <code></p>
  
  <pre><code>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);
  </code></pre>
  
  <p></code>
  <br>Result is: 21-1-2000 not 21-01-2000 as expected.
  <br>
  When adding additional to_date(,) as: <br></p>
  
  <pre><code>to_date(day||'-'||month||'-'||year,'DD-MM-YYYY') as formatted_date
  </code></pre>
  
  <p>it doesn't  even change day and month fields with eachother.</p>