Error converting data type varchar to datetime when running raw SQL but not when called by LINQ to SQL

Question

A client is getting an error when running my code. They sent me the SQL from profilder.

When I paste it into SQL Server Management Studio it fails with: Error converting data type varchar to datetime

However it doesn't fail when I run it on my local dev box or another clients production sever.

To test I created a simple app with a L2S datacontext containing one entity that looks something like:

public class UserAccount 
{
    public int Id { get; set; }
    public string Username { get; set; }
    public DateTime? LastActivity { get; set; }
}

Insert a record and then update it:

var account = db.UserAccounts.Single(a => a.Username == "Mojo");
account.LastActivity = DateTime.Now;
db.SubmitChanges();

The records gets updated in the database. But when I grab the T-SQL from Profiler:

exec sp_executesql N'UPDATE [UserAccount] SET [LastActivity] = @p2 WHERE ([Id] = @p0) AND ([Username] = @p1) AND ([LastActivity] IS NULL)',N'@p0 int,@p1 nvarchar(4),@p2 datetime',@p0=1,@p1=N'Mojo',@p2='2009-11-10 14:04:41.7470000'

and execute it in SQL Server Management Studio I get: Error converting data type varchar to datetime

What am I missing?

Answer 1

Are you using identical connections in Management Studio as in your test app? It sounds like this isn't the case the connections have different regional settings, causing the date to be parsed differently.

Answer 2

The problem is with the extra zeroes at the end of the date:

'2009-11-10 14:04:41.747' (works)
'2009-11-10 14:04:41.7470000' (error converting...)

The four extra zeroes are probably just visible in the Visual Studio debugger. LINQ does not sent them to SQL Server.

Answer 3

Different versions of SQL Server perhaps?

The extra zeros in the date would make it a datetime2 type which would be acceptable in 2008 but not 2005.

Answer 4

DATETIME should have only 3 digits after second, DATETIME2 has 7 digits after second. Seems that the string is formatted for DATETIME2.

Answer 5

Hmm.. I'd like to say that I'm having a similar issue, but in my case, my database is not updated, and I get an exception back.

The answer in my case is will certainly be to remove the extra zeros, but in our mapping file the column type is marked having a DbType="DateTime" and the database column is of type datetime. Lastly, our poco has a nullable DateTime field.. I'm not sure how to get rid of those extra zeros..

Does anyone have any ideas?