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

User · Accepted Answer

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

User · Answer

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

User · Answer

select to char to date  1 21 2000   mm dd yyyy    dd-mm-yyyy   from dual

[oracle] Oracle to_date, from mm/dd/yyyy to dd-mm-yyyy

Examples related to oracle