Sometimes we waste a lot of time trying to get to the root cause of exceptions. Some exceptions are so misleading that they end up sending you on a wild goose chase.
When I encountered the following exception during SQL Server data importation process,
Invalid character value for cast specification.
The wording led me to believe that one of the characters had a casting problem. Going through the code, I could not locate any place where I was casting single characters. All the columns were being casted correctly and none was throwing an exception. So what was causing the exception?
It turned out that one of the date columns though casting without errors to 0001-01-01 was being rejected while being persisted to the database. Why then not tell me it is a date and not a character?
In hindsight, I recalled the inconsistencies of date values between .NET and SQL Server. I have been a victim of using DateTime.MinValue only for it to be rejected by SQL Server with the following exception.
SqlDateTime overflow. Must be between
1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
SQL Server 2008 and above has resolved these inconsistencies by the introduction of Date, DateTime2 and DateTimeOffset types. But woe unto you if you are still lagging behind like me. You will always have to bear with these types of errors or check and convert the DateTime.MinValue values to NULLs before persisting them to the database.