Linq DateTime.UtcNow and SQL DateTime Datatype

Recently I have being using SQLMetal that generates dbml and the backing code. I encountered a problem that was obtuse, apparently good code was failing to execute. The message from Linq was "Row not found or changed".

 

Launching SQL Profiler showed me the SQL

 

exec sp_executesql N'UPDATE [WebApp].[OperationInfo]
SET [WorkflowResultsID] = @p8
WHERE ([MachineVersionID] = @p0) AND ([OperationID] = @p1) 
AND ([ExecutionDate] = @p2) AND ([OperationType] = @p3) AND ([WorkflowResultsID] IS NULL) 
AND ([ConfigurationResultsID] IS NULL) AND 
([SequenceResultsID] IS NULL) AND ([ExecutionGuids] IS NULL)
 AND ([WorkRequestID] IS NULL) AND ([LastStep] = @p4) AND ([TrafficRuleCommitStatus] = @p5) 
 AND ([CreatedUTC] = @p6) AND ([LastModifiedUTC] = @p7)',
 N'@p0 bigint,@p1 bigint,@p2 datetime,@p3 smallint,@p4 varchar(12),@p5 smallint,@p6 datetime,@p7 datetime,@p8 bigint',@p0=1,@p1=6,@p2='2010-08-05 09:56:47.2470000',@p3=1,@p4='Requirements',@p5=0,@p6='2010-08-05 09:56:47.2470000',@p7='2010-08-05 09:56:47.2470000',@p8=3

Manually executing, I got:

 

Msg 241, Level 16, State 1, Line 4

Conversion failed when converting date and/or time from character string.

 

Looking at the TSQL, I said huh???!!!???

 

I then tried an experiment.

 

Declare @p2 datetime

set @p2='2010-08-05 09:56:47'

set @p2='2010-08-05 09:56:47.247'

set @p2='2010-08-05 09:56:47.2470'

set @p2='2010-08-05 09:56:47.24700'

set @p2='2010-08-05 09:56:47.247000'

set @p2='2010-08-05 09:56:47.2470000'

The red ones gave the above error messages.

 

Tried it with:

 

Declare @p2 datetimeoffset

set @p2='2010-08-05 09:56:47'

set @p2='2010-08-05 09:56:47.247'

set @p2='2010-08-05 09:56:47.2470'

set @p2='2010-08-05 09:56:47.24700'

set @p2='2010-08-05 09:56:47.247000'

set @p2='2010-08-05 09:56:47.2470000'

Set @p2='2010-08-05 09:56:47.2470000'

Set @p2='2010-08-05 09:56:47.2470000'

Everything worked....

then with

Declare @p2 datetime2

set @p2='2010-08-05 09:56:47'

set @p2='2010-08-05 09:56:47.247'

set @p2='2010-08-05 09:56:47.2470'

set @p2='2010-08-05 09:56:47.24700'

set @p2='2010-08-05 09:56:47.247000'

set @p2='2010-08-05 09:56:47.2470000'

Set @p2='2010-08-05 09:56:47.2470000'

Set @p2='2010-08-05 09:56:47.2470000'

Everything worked....

 

In the C# code, I had changed the code from

 

OperationInfo.CreatedUTC=DateTime.Now;

 

to

 

OperationInfo.CreatedUTC=DateTime.UtcNow;

 

This change was causing the problem!

 

The solutions are

• OperationInfo.CreatedUTC=DateTime.Now.ToUniversalTime();
• Changing the database column types.