How to Fix SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM
Here's how you can fix the "SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM" error in SQL Server 2008 or later.
Tuesday, April 24, 2012
So here's a quick tip on how to fix the following SQL Server "SqlDateTime overflow" error.
SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
The reason that you are probably getting this error is because you are trying to insert a datetime in SQL Server that is before the date 1/1/1753 and your using the SQL Server Data Type "datetime" in the column you are inserting the date value. The standard "datetime" Data Type column in SQL Server has a limit from 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
To get around this issue if your using SQL Server 2008 or later, you'll want to change your SQL Server table column Data Type to use "datetime2(7)" instead of "datetime". The "datetime2(7)" Data Type is new to SQL Server 2008 and let's you insert values between the date range of 01/01/0001 through 12/31/9999 and time rage of 00:00:00 through 23:59:59:9999999. Unfortunately, if you're using an older version of SQL Server (like 2005), then you're out of luck using this method outlined above.
Also in your C# code (or whatever language you use), make sure to also change the Data type to "DbType.DateTime2" or "SqlDbType.DateTime2", instead of the regular "DbType.DateTime".