I run into an interesting conversion situation when a vendor returned some birthdate data in a text format and I needed to convert it to Datetime to insert the records into the data warehouse. The obvious Cast and Convert did not work. They generated errors.
After doing some research I found a solution for this case. The first error was due to the dates being out of range. In other words some dates included 00 E.g. (00/00/1950) and some 02/29 was not during a leap year. The first line of code corrects these issue. Note that Where clause prevents years from being changed when fixing 00 in the string.
Then, leap year is fixed, follow by adding a new empty date column and extracting dates from a character string into a date column.
