DATE is a temporal data type. It has no format per se. Did some moron put a string in a column? First, find the moron and beat him, and replace all of his code. He is dangerously stupid.
Next, alter the table so you have a DATE column. Load it with CAST CONVERT(VARCHAR(10), stupid_date, 112) AS DATE). Alter the table to drop the original stupid date column.
--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
The date format was fine, it is just a date column; no string attached. I just need to create a column with the YYYYMMDD format. This allows me to join it to a fact table which has a DateKey column with that format.
Nevertheless, your method will prove handy should I need to convert a stupid_date created by a moron to a DATE column.
This is a great resource for all things pertaining to dates in SQL Server:
Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.