sql - How to convert yyyymmdd to dd-mm-yyyy in Oracle? -

i have column contains dates in varchar2 varying formats such 19.02.2013, 29-03-2013, 30/12/2013 , on. annoying of 20130713 (which july 13, 2013) , want convert dd-mm-yyyy or dd-mon-yyyy.

if column contains various formats, you'll need deal each one. assuming question includes all known formats, have couple of options.

you can use to_char/to_date. dangerous because you'll sql error if source data not valid date (of course, getting error might preferable presenting bad data).

or can rearrange characters in string based on format. little simpler implement, , doesn't care delimiters are.

method 1:

   case when substr(tempdt,3,1)='.'         to_char(to_date(tempdt,'dd.mm.yyyy'),'dd-mm-yyyy')         when substr(tempdt,3,1)='-'         tempdt         when length(tempdt)=8         to_char(to_date(tempdt,'yyyymmdd'),'dd-mm-yyyy')         when substr(tempdt,3,1)='/'         to_char(to_date(tempdt,'dd/mm/yyyy'),'dd-mm-yyyy') 

method 2:

case when length(tempdt)=8      substr(tempdt,7,2) || '-' || substr(tempdt,5,2) || '-' || substr(tempdt,1,4)      when length(tempdt)=10      substr(tempdt,1,2) || '-' || substr(tempdt,4,2) || '-' || substr(tempdt,7,4) end 

sqlfiddle here