Linq-to-SQL and DateTime weirdness

Question

We have really strange and inconsistent behavior with Linq-to-SQL here.

Our application is installed at quite a few customers' sites, and it works just fine for the most part. One of the queries in Linq-to-SQL updates a table and set a DateTime column to a new value.

In all the cases - including our development and test systems - this Linq-to-SQL statement gets translated into something along the lines of:

UPDATE dbo.OurTable 
SET WorkTimeStamp = @WTS 
WHERE ID = @ID

@WTS = '2011-11-04 14:15:25', @ID = 555

However, at one customer's site, for reasons that aren't clear to us (yet), this update gets translated into:

UPDATE dbo.OurTable 
SET WorkTimeStamp = @WTS 
WHERE ID = @ID

@WTS = 'Nov  4 2011 02:15:25PM', @ID = 555

and for some reason then fails on SQL Server 2005.

Now, that customer's servers (web server and SQL Server) have US-English versions of Windows Server 2008 installed; the language in SQL Server is set to us_english, the date format is set to mdy, the user account running the update has its language set to English in SQL Server ..... and that setup is the same elsewhere (for example, on our test server infrastructure).

So my question really is:

1. Why on earth does Linq-to-SQL suddenly create a totally different representation of the same DateTime to send to SQL Server? Is there any knob to turn to control this?

2. And why can't ADO.NET and the SQL Server 2005 SP2 database handle that UPDATE statement correctly? We're getting an error in our log that reads:

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

It seems to be a .NET error (more than a SQL Server error) and it seems as if .NET cannot really interpret that Nov 4 2011 02:15:25PM as a valid DateTime for some reason. When attempting to run the generated UPDATE statement in SQL Server Management Studio, we cannot seem to "force" that error to happen - the UPDATE happily works just fine.....

Update: some further investigation seems to indicate Linq-to-SQL behaves differently when going against SQL Server 2005 or 2008.

• with SQL Server 2005, our dates get turned into: Nov 4 2011 02:15:25PM
• with SQL Server 2008, our dates get turned into: 2011-11-04 02:15:25PM

Answer 1

I think you might the chasing the wrong problem.

I would first check:

1. Your LINQ to SQL schema/database model is accurate.
2. Your problem logic to make sure there is no way the new DateTime value can be out of range. In particular, check that it can not be either DateTime.MinValue or DateTime.MaxValue.
3. That you aren't doing any string to date parsing in your application.
4. That the SQL Server does not have any triggers (particularly instead of triggers, which might be modifying the update statement).

I am guessing you (or your customer) starting by getting the 'SqlTypeException - SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM' error message and upon investigating you noticed the difference in the way that the dates are displayed.

You don't mention where the information is coming from, so I assuming something like SQL profiler.

However the date display issue may be a red-herring as it should not be a problem.

with SQL Server 2005, our dates get turned into: Nov 4 2011 02:15:25PM

with SQL Server 2008, our dates get turned into: 2011-11-04 02:15:25PM

I'm not sure exactly what you mean by that. SQL does not 'turn' dates into string as it does not store dates as strings, but the internal representation is a number (something like the number of days since 1st Jan 1900).

If you mean your dates get displayed as Nov 4 2011 02:15:25PM, then that is up to the program that is displaying the information.

Also, as I understand it, if you are using a DateTime parameter (which LINQ to SQL should be doing if the database model is accurate), then the information that is sent from the client to the SQL Server is the SQL numeric representation of the DateTime. This should avoid any datetime conversion issues between the client and server. When you look at, for example, SQL Profiler, it does not show you the numeric representation of the date, which will mean very little to most people, but tries to be helpful and displays the value as a string.

The important point is that if SQL or SQL profiler manages to display a datetime parameter as 'Nov 4 2011 02:15:25PM' then it knows it is a valid date, and it knows exactly what date that is.

So I would suspect that the display format issue is probably irrelevant.

That then leaves the question as to why your customer is getting the SqlTypeException - SqlDateTime overflow error message.

The first thing to do is to check what date value you are setting, which needs to be done at the application level and not at the SQL Server server since it would not get that far. (This is another reason why I don't think this is a SQL configuration issue.)

it seems as if .NET cannot really interpret that Nov 4 2011 02:15:25PM as a valid DateTime for some reason

I don't see where .NET would be even trying to interpret strings as date unless you have some DateTime.Parse commands and if that is the case, then the problem has nothing to do with either LINQ or SQL.