Unfortunately, I am not working in a SQL Server 2012 environment to use try_parse. I did run the suggested script to break apart the Year, Month, and Day, and did an ORDER BY Year, Month, and Day, where the field I am parsing IS NOT NULL. I got 268 rows that came back, and there was no dates that were out of the ordinary, such as a Month with a 13; a Day with a 32, or even a Month with an 11 (November) and Day that was 31, for example, where the date would not make sense to the system. Even a Month of 02 (February) had less than 29 for any of the correlating Day fields!

As an update...after parsing the Year, Month, and Day data, and determining that no "out of ordinary" date values existed, I put it all together in one field as such:

SELECT CONVERT(datetime, ('20' + LEFT(a1.Field1,2)) + (SUBSTRING(a1.Field1,3,2)) + (SUBSTRING(a1.Field1,5,2))) as ConvertedDate

This works just fine. So, I copied/pasted that CONVERT statement into my CASE statement as such:

CASE WHEN a1.Field1 IS NOT NULL THEN CONVERT(datetime, ('20' + LEFT(a1.Field1,2)) + (SUBSTRING(a1.Field1,3,2)) + (SUBSTRING(a1.Field1,5,2)))

This fails with the message, "Conversion failed when converting date and/or time from character string." I am not sure why the CASE statement would cause it to fail, especially since I am weeding out those values in Field1 that are NULL.