Geek Answers Handbook

System.Data.SqlTypes.SqlTypeException: SqlDateTime Overflow

I work with C # .net as well as SQL Server 2008.

I have the following error while trying to run a test module in my project.

System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM..

Database table

 Column Name: createddate Type: datetime Default Value: (getdate()) Allow Nulls: No.

I do not want to insert the created date as part of my INSERT request.

When I manually enter some data into the database table, I get the following error:

  The row was successfully committed to the database. However, a problem occurred when attempting to retrieve the data back after commit. Because of this the displayed data within the row is read-only. To fix this problem, please re-run the query.

I do not understand why I am getting this error and cannot find those who had this problem. Can anyone help?

+4
source share
5 answers

Matt is most likely on the right track. You have defined a default value for your column, however this will only take effect if you really insert something into your table in the database.

When you do a unit test, as you say, you most likely initialize the DateTime variable with something (or not), then it will be DateTime.MinValue, which is 01/01/0001), and then you submit to SQL Server, and this value is out of range for DATETIME on SQL Server (as the error clearly indicates).

So, you need to add a line to your .NET unit test to initialize the DateTime variable to "DateTime.Today":

 DateTime myDateTime = DateTime.Today

and then paste this into SQL Server.

OR: you can modify the SQL INSERT statement so that it does not insert a value for this column - it looks right now, it does it (and tries to insert it - for SQL Server - the date is incorrect in the table). If you do not specify this column in your INSERT, then the default column getdate() will start and insert today's date into the column.

Mark

+6
source

Do you use Linq for SQL to write unit test?

If so, it could be bypassing the default getdate () value and using another value that is out of range.

+3
source

I have the same problem, I used Linq with DataClasses files automatically generated by VS2010. I realized that Linq does not use the default value ( GetDate() ), but it will send a Datetime.Min value. As a result, the SQL server will reject this datetime "01/01/0001" because it is not in range.

What I did to fix this is to always set the value to Datetime.Now or Datetime.Today , and so Linq will no longer send a bad Min Datetime value.

+2
source

You are probably getting an overflow error in your unit test because you are passing an uninitialized DateTime with a DateTime.MinValue value that is outside the valid range for SQL datetime.

I think I saw this error message when changing the table manually, this is not a problem, just refresh the table.

0
source

Are you using a data context class setting with a DBML file and all this jazz? If so, you can click on the field in your DBML file and set the Auto Generated Value property to True. Visual Studio should be aware of this using the Pkey fields, but must be set to "timestamping" for these actions.

0
source

More articles:


All Articles