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.
By
So here's a quick tip on how to fix the following SQL Server "SqlDateTime overflow" error.
System.Data.SqlTypes.SqlTypeException
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". 


0
1

1 Comment

anonymous by Abhishek on 1/6/2014
Thanks ..:)

Add your comment

by Anonymous - Already have an account? Login now!
Your Name:  

Comment:  
Enter the text you see in the image below
What do you see?
Can't read the image? View a new one.
Your comment will appear after being approved.


Related Posts


When using SQL Server 2008, you may receive a Save (Not Permitted Dialog) box pop-up when trying to save changes to an existing table. The dailog box reads: "Saving changes is not permitted. The changes you have made require the following tables to be...  more »

Here's one way to setup your connection string for a SQL Server 2008 Express database for a live website using ASP.NET on Windows Server 2008. Open "Sql Server Configuration Manager" -> the click on "SQL Server Network Configuration" -> then click...  more »

I recently installed SQL Server 2008 database on my local computer and forgot to enable the common language runtime (CLR). Because I had forgotten to turn it on, some portions of my sites were not fuctioning properly due SQL Server not being able to...  more »

Here's a couple quick SQL database tips for finding and deleting duplicate values in a SQL Server table.To find duplicates in a Column use the following SQL: SELECT ColName1, COUNT(*) TotalCountFROM TableName GROUP BY ColName1 HAVING (COUNT(ColName1) >...  more »

Here's a step by step process to enable a remote connection to your SQL Server 2008 Express database that is configured on Windows Server 2008 using an assigned port #. 1) First make sure the SQL Server Authentication is enabled and the User name that...  more »