Sometimes, string-to-date conversion in SQL Server results in an error, not because of the date or time formats used, but because you’re attempting to store incorrect information that’s not acceptable ...

The root cause of error is when trying to convert a character string to one of the datetime related types in SQL when the format of the string does not identify the requested type correctly.