Conversion from mm/dd/yyyy to yyyy-mm-dd

Database
N/A

Conversion from mm/dd/yyyy to yyyy-mm-dd

Hi,

Can anyone tell me how to convert char field in format mm/dd/yyyy(allowing for single digits in mm and dd fields)  to "CCYY-MM-DD" . IF date is invalid use '1900-01-01'.

Thanks

1 REPLY

Re: Conversion from mm/dd/yyyy to yyyy-mm-dd

Hi,

This should work.

Select '1/2/1900' as date1,

TO_CHAR(TO_DATE (

case   

   
when strtok(date1, '/', 2) between 1 and 9 then strtok(date1,'/', 3)||'/0'||strtok(date1, '/', 1)||'/'||strtok(date1,'/', 2)

   
else  strtok(date1, '/', 3)||'/'||strtok(date1, '/', 1)||'/'||strtok(date1,'/', 2)

end , 'YYYY/MM/DD'), 'YYYY-MM-DD') as "YYYY-MM-DD"

Thanks,

Dinesh