System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow

Question

I am working with C#.net and also SQL Server 2008.

I have the following error, when trying to run a test unit within 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 don't want to insert the createddate as part of my INSERT query.

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 don’t understand why I am getting this error and cannot find anyone who has had this problem. Can anyone help?

Answer 1

You are probably getting an overflow error in your unit test because you are passing in an uninitialised DateTime with the value DateTime.MinValue which is outside the allowable range for SQL's datetime.

I think I have seen that error message when modifying the table manually, it isn't a problem, just refresh the table.

Answer 2

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

When you do a unit test, as you say, you most likely initialize the DateTime variable to something (or not - then it'll be DateTime.MinValue, which is 01/01/0001) and then you send that to the SQL Server and this value is outside the valid range for a DATETIME on SQL Server (as the error clearly states).

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

DateTime myDateTime = DateTime.Today

and then insert that into SQL Server.

OR: you can change your SQL INSERT statement so that it does not insert a value for that column - it looks like right now, it does do that (and attempts to insert that - for SQL Server - invalid date into the table). If you don't specify that column in your INSERT, then the column default of getdate() will kick in and insert today's date into the column.

Marc

Answer 3

Are you using Linq to SQL to write your unit test?

If you are, it might be bypassing the getdate() default value, and using some other value instead which falls outside the valid range.

Answer 4

are you using a data context class setup with the DBML file and all that jazz? If so, then you can click on the field in your DBML file and set the "Auto Generated Value property" to True. Visual Studio must already know to do this with the Pkey fields, but has to be set for these "timestamping" sort of actions