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

Question

I have all the dates inserted into the table as varchar2 (10) and formatted as 'mm / dd / yyyy'. I need the following format "mm-dd-yyyy" and the date data type. My implementation without PLSQL will 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); 

code>
Result: 21-1-2000 is not 21-01-2000 as expected.
When adding extra to_date (,) like:

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

he does not even change the field of the day and month with eachother.

Answer 1

I suggest you use TO_CHAR() when converting to a string. To do this, you must first create a date.

 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 ); 

Answer 2

You do not need to guess with extracting parts of the date. Just add it to the date using to_date and the format in which it is stored, then convert that date to char in the format you need. Like this:

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

Answer 3

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