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'.
This should work.
Select '1/2/1900' as date1,
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"