Tip: Convert dates stored as text into date values

This is a tip for SQL server – if you have a database where dates are stored as text and want to convert them all to date values.

1. Create a new column for the date value – columnName_asDate

2. Run the following script


UPDATE table_name
SET columnName_asDate = CONVERT(DATETIME, RIGHT(columnName, 4) + SUBSTRING(columnName, 3, 2) + LEFT(columnName, 2), 112)