I am facing the above issue which is mentioned by zenith. i have tried with the all possible scenarios which was provided above. there is no improve.
Now i got the solution. its working fine now.
Sol: just put one cast in the select part.
INSERT INTO sandbox_db.date_experiment
SELECT CAST (column_A as DATE FORMAT 'dd-mmm-yy')
2-digit year is always tricky to handle. Causing issues with leap-year-february, or confusion between year 20** or 19**. Do check your data after insert if its correct.