SELECT CONVERT (VARCHAR (10), CONVERT (DATETIME, '20161231'), 101) AS [mm/dd/yyyy]
Ye gods, why? Why would you do this? Store dates as dates, and then you don't run into these problems, and deal with formatting in the presentation layer.
Apart from anything else, you'll get better response times, save space, and not be tied to a brain-dead middle-endian date format. ISO8601 FTW.
18 People are following this question.