Linq-to-SQL and DateTime weirdness

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.