convert date in string format to date datatype sql
You can use SUBSTRING to pull apart the source. Then DATEFROMPARTS will stitch them back together.
A few notes: dates are not held internally in that format, or any other date-like format. The question says the source is 6 characters but the example shows 8. If you need to find the century you'll have to decide on rules for that.