DateTime2 in SQL Server merging different at the nanoseconds precision from C#

Question

I am having an issue with DateTime inserting into SQL Server DateTime2(6) data type. I have a variable in C# defined as Datetime :

public DateTime CreatedOn { get; set; }

Below is the the date that I am passing to SQL Server:

When you notice the ticks on createdon is ending with 786066. But I merged the above datetime to SQL Server and after Update I am getting different nanoseconds:

The last four digits are getting changed. However when I do a Bulk copy on to the table the dates are inserted just fine. But when I merge row by row, the nano seconds are merged incorrectly.

MERGE [dbo].[ItemAttribute] as tgt
USING 
    (VALUES(@FK_ItemAttributeGroupCode, @ItemAttributeCode, @Name, @Description, 
            @EffectiveDate, @TerminationDate, @DataType, @DefaultString, 
            @DefaultNumeric, @DefaultDate, @FK_ItemGroupTypeCode, 
            @IsPublishable, @AttributeType, @IsAutoDelete, @IsFacilitySpecific, 
            @CreatedBy, @CreatedOn, @LastUpdatedBy, @LastUpdatedOn)) AS src ([FK_ItemAttributeGroupCode], [ItemAttributeCode], [Name], [Description], [EffectiveDate], [TerminationDate], [DataType], [DefaultString], [DefaultNumeric], [DefaultDate], [FK_ItemGroupTypeCode], [IsPublishable], [AttributeType], [IsAutoDelete], [IsFacilitySpecific], [CreatedBy], [CreatedOn], [LastUpdatedBy], [LastUpdatedOn])
     ON tgt.[FK_ItemAttributeGroupCode] = @FK_ItemAttributeGroupCode AND tgt.[ItemAttributeCode] = @ItemAttributeCode

WHEN MATCHED AND (EXISTS (SELECT TGT.[Name], TGT.[Description], TGT.[EffectiveDate], TGT.[TerminationDate], TGT.[DataType], TGT.[DefaultString], TGT.[DefaultNumeric], TGT.[DefaultDate], TGT.[FK_ItemGroupTypeCode], TGT.[IsPublishable], TGT.[AttributeType], TGT.[IsAutoDelete], TGT.[IsFacilitySpecific], TGT.[CreatedBy], TGT.[CreatedOn], TGT.[LastUpdatedBy], TGT.[LastUpdatedOn] EXCEPT SELECT @Name, @Description, @EffectiveDate, @TerminationDate, @DataType, @DefaultString, @DefaultNumeric, @DefaultDate, @FK_ItemGroupTypeCode, @IsPublishable, @AttributeType, @IsAutoDelete, @IsFacilitySpecific, @CreatedBy, @CreatedOn, @LastUpdatedBy, @LastUpdatedOn))  
    THEN
       UPDATE 
           SET [FK_ItemAttributeGroupCode] = @FK_ItemAttributeGroupCode, [ItemAttributeCode] = @ItemAttributeCode, [Name] = @Name, [Description] = @Description, [EffectiveDate] = @EffectiveDate, [TerminationDate] = @TerminationDate, [DataType] = @DataType, [DefaultString] = @DefaultString, [DefaultNumeric] = @DefaultNumeric, [DefaultDate] = @DefaultDate, [FK_ItemGroupTypeCode] = @FK_ItemGroupTypeCode, [IsPublishable] = @IsPublishable, [AttributeType] = @AttributeType, [IsAutoDelete] = @IsAutoDelete, [IsFacilitySpecific] = @IsFacilitySpecific, [CreatedBy] = @CreatedBy, [CreatedOn] = @CreatedOn, [LastUpdatedBy] = @LastUpdatedBy, [LastUpdatedOn] = @LastUpdatedOn

WHEN NOT MATCHED 
   THEN
      INSERT([FK_ItemAttributeGroupCode], [ItemAttributeCode], [Name], [Description], [EffectiveDate], [TerminationDate], [DataType], [DefaultString], [DefaultNumeric], [DefaultDate], [FK_ItemGroupTypeCode], [IsPublishable], [AttributeType], [IsAutoDelete], [IsFacilitySpecific], [CreatedBy], [CreatedOn], [LastUpdatedBy], [LastUpdatedOn]) 
      VALUES(@FK_ItemAttributeGroupCode, @ItemAttributeCode, @Name, @Description, @EffectiveDate, @TerminationDate, @DataType, @DefaultString, @DefaultNumeric, @DefaultDate, @FK_ItemGroupTypeCode, @IsPublishable, @AttributeType, @IsAutoDelete, @IsFacilitySpecific, @CreatedBy, @CreatedOn, @LastUpdatedBy, @LastUpdatedOn);

We are using SQL Server 2016.

foreach (var entity in entities)
                {
                    try
                    {
                        await conn.ExecuteAsync(sql, commandType: CommandType.Text, param: entity, commandTimeout: SQLCommandTimeOut);
                    }
}

The SQL has the above merge query, I am passing entity as parameter and CreatedOn on the entity is DateTime. However instead of looping and inserting into table, if we use BulkCopy of C#, it is inserted properly with the actual DateTime C# has up to nano seconds, but row by row insert last 4 digits of nano seconds are different if you notice above screenshots.

I can't read the value from your first image - maybe post an enlarged version. — Dale K, Jun 19 '19 at 21:14
Seems like you're using a `datetime` somewhere. In older versions of SQL Server, a `datetime` ending in `.007` would have also shown `.007000` in a `datetime2(6)`, but that changed in SQL Server 2014 (if I recall correctly), and now appropriately rounds to 1/300 of a second. ([db<>fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=452db8c7d554386ac266bf278ece5528) that doesn't show what I want, as it hides the precission, so you'll have to copy and paste it out.) — Thom A, Jun 19 '19 at 21:14
And what do you mean by merged? What actual SQL are you executing? — Dale K, Jun 19 '19 at 21:14
In your merge SQL @CreatedOn is an SQL Variable. Please post how it is initialized. — Piotr Palka, Jun 19 '19 at 21:22
Considering the lack of reply, I feel like the OP has discovered that `@CreatedOn` is a `datetime`. :/ — Thom A, Jun 19 '19 at 21:45
Perhaps the discussion [here](https://social.msdn.microsoft.com/Forums/silverlight/en-US/de5dbf3e-8c95-40f4-9e31-b71f1f31983d/change-in-datetime2-implementation-in-sql-server-2016?forum=transactsql) is related. It talks about a change in 2016 regarding increased precision when re-interpreting datetime values as datetime2. — SMor, Jun 19 '19 at 22:46
[Yesterday I've answered a very much related question to this one, though I'm not convinced it's a duplicate](https://stackoverflow.com/questions/56646696/how-to-avoid-loosing-precision-using-implicit-conversion-sqlparameter-with-dat/56646754#56646754) — Zohar Peled, Jun 20 '19 at 04:57
Also, try changing `DateTime2(6)` to `DateTime2(7)`, see if that makes a difference. — Zohar Peled, Jun 20 '19 at 04:59
@ZoharPeled i am using dapper to insert into SQL server DB and we have Inline query which gets execute using Dapper in C#. The datatype as I mentioned is DateTime in C# which I read it from a different database source(Db2) which is read correctly when I look at the Dto, but when i use inline query to Merge by passing entity values using dapper, after inline query execution the datetime2(6) in SQL server has different value in last 4 digits of nano seconds. However this is problem only with inline query and row by row insert, If I am doing BulkInsert then the dates get inserted correctly. — VR1256, Jun 20 '19 at 13:04
Well, if you could share the c# code it might help finding a solution. — Zohar Peled, Jun 20 '19 at 14:41

DateTime2 in SQL Server merging different at the nanoseconds precision from C#

0 Answers0