8

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?

Improve this question
1
  • could you run getdate() in the query analyzer of management studio? see what it returns. Maybe this is a locale problem... Commented Aug 4, 2009 at 11:10

5 Answers 5

Reset to default
9

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

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Any professional way of passing an empty .NET datetime to the Entity Framework in order to avoid this error? Because sometimes, setting a date like Today or Now is not suitable.
@MarcRoussel: if your EF entity's column is defined as DateTime? (nullable DateTime) - then yes - just pass in null. Otherwise, no - since the non-nullable DateTime type in .NET cannot handle "null" or "empty", really ...
I understand. So, there's nothing like a simple conversion then?
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.

Improve this answer

1 Comment

How would one fix that?
2

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.

Improve this answer

Comments

0

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.

Improve this answer

Comments

0

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

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.