Just to make sure I haven't been going crazy I have already tried the same code with a literal string value such as '20081201' and it works fine. It just fails when I attempt to convert values from that column.
Thanks for getting the old wheels going. The table has over 1 million records and were originally imported from an Access database. I assumed it must of have proper data integrity...stupid me.
When I tried the conversion when checking for a 1 or 2 for the beggining of the year it worked fine. Which got me thinking there must be some dates that were out of range. I used substring to check if the any day were over 31 but that was ok...then I check if any months were above 12 and still no problem. Last I tried the year and then BAM I discovered a few hundred records did not even have numbers for the year.
Now that I know that some of the data is invalid this will be a cinch!
Thank you for playing, PMASchmed, we have some lovely parting gifts for you including a copy of DBForums.com the home game, and a year's supply of Turtle Wax (how many turtles do these people wax in a year, anyway???).
Seriously, though, you were on the right track. It was more a matter of finding the bad apples.