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

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 2

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 3

I got the same issue, I was using Linq with the DataClasses files automatically generated by VS2010. The I realised that Linq is not using the default value (GetDate()) but it will send a Datetime.Min value. As the consequence the SQL server will reject this datetime "01/01/0001" because it's not in range.

What I did to fix it is to always set the value to Datetime.Now or Datetime.Today and so Linq will not send the bad Min Datetime value anymore.