Why are you converting to datetime and then back to varchar? Why varchar instead of char?

SQL Server won't recognize the MMDDYYYY format as a valid date, you need to supply the slashes and preferably use convert with the format code.

Here's something to help you out.

SELECT Substring(DOB, 1, 2) + '/' + Substring(DOB, 3, 2) + '/' + Substring(DOB, 5, 4) AS DOB

,STUFF( STUFF( DOB, 3, 0, '/'), 6, 0, '/') AS DOBAlternateFormula

,CONVERT(datetime, STUFF( STUFF( DOB, 3, 0, '/'), 6, 0, '/') , 101) AS DOBDatetime

,CONVERT(VARCHAR(10), CONVERT(datetime, STUFF( STUFF( DOB, 3, 0, '/'), 6, 0, '/') , 101), 101) DOBrevision

FROM names