GotKnowHow.com

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.


Did this article help you out, share it with your friends:
Tagged
sql server (14)

Ask a Question

140 characters left