How to avoid loosing precision using implicit conversion (SqlParameter) with DateTime (C#) even if I am using DateTime2 in my SQL Server?

Question

This is my definition column in my database.

I have a problem with DateTime in C# and the value that is being saved into my database SQL Server.

When the applications sends: 2019-06-14 17:10:39.0192950

Saves into SQL Server this: 2019-06-14 17:10:39.0200000.

And When I need to check if the file already exists in my database using the following function:

protected Func<DateTime?, DateTime?, bool> TimeEquals = (x, y) => 
  x.Value.Hour == y.Value.Hour 
     && x.Value.Minute == y.Value.Minute
        && x.Value.Second == y.Value.Second && x.Value.Millisecond == y.Value.Millisecond;

That is my code when I insert the values into my SQL Server database:

            foreach (var parameter in parameters)
            {
               command.Parameters.Add(new SqlParameter(
                        parameter.Label,
                        parameter.Value
                    ));
            }

I start having problems loosing precision. What is the best way to not loose precision when dealing with dates between C# and SQL Server?

Use `DATETIME2` in your tables; `DATETIME` is only accurate to (about) three milliseconds, with weird rounding behavior to boot. — Jeroen Mostert, Jun 18 '19 at 10:11
you need to try changing the SQL parameter type to datetime2 in your c# code, at the moment your parameter is not given a type - it's probably best always to do so — Cato, Jun 18 '19 at 10:58
Are people sure that the alternative question definitely is the answer in this case? — Cato, Jun 18 '19 at 10:58

Zohar Peled · Accepted Answer · 2019-06-18T10:59:18.333

4

Update

After establishing the fact that you are using DateTime2(7) to store the data in your database, the next hypotheses was naturally that somewhere in your code, the data gets stored in an SQL Server's DateTime.

Since Data type precedence puts DateTime2 in a higher precedence than DateTime, it's clear that it's not an implicit conversion inside SQL.

That leaves us with the only option of an implicit conversion to SQL Server's DateTime somewhere between the c# code and the SQL Server code - and sure enough, here it is:

command.Parameters.Add(new SqlParameter(
    parameter.Label,
    parameter.Value
));

The problem with this code is the same problem that the AddWithValue method has - since we don't supply the data type, The ADO.Net driver has to guess it - and just like written the article - it guessed wrong.

The correct way to add parameters to a command is using one of the Add overloads that takes in both the parameter name and the data type:

command.Parameters.Add("@ParamName", SqlDbType.DateTime2).Value = CSDateTimeInstance;

Due to the lack of context, I'm not sure how you can incorporate that into your code, but that is the correct solution for this case.

First version

In Sql Server, use DateTime2.
You are probably using DateTime which has the precision of 1/300th of a second, while DateTime2 has the precision of 100ns.

From the DateTime page:

Accuracy Rounded to increments of .000, .003, or .007 seconds.

From the DateTime2 page:

Precision, scale 0 to 7 digits, with an accuracy of 100ns. The default precision is 7 digits.

edited Jun 18 '19 at 10:59

answered Jun 18 '19 at 10:11

Zohar Peled

79,642
10
69
121

Are you *sure*, @FlavioFrancisco, `SELECT CONVERT(datetime, CONVERT(datetime2(7),'2019-06-14 17:10:39.0192950'));` returns value you are saying you have. Perhaps you're using a `datetime` parameter? – Thom A Jun 18 '19 at 10:13
2

@Larnu If developers would not be nitpicky, who will? thanks, edited that in. – Zohar Peled Jun 18 '19 at 10:18
1

@FlavioFrancisco Well, if you are using `DateTime2` in your tables, it means that somewhere between the c# and the database table the value is stored in SQL Server's `DateTime` data type. It might be in a stored procedure, it might be an implicit conversion - you will have to check your code to know for sure. – Zohar Peled Jun 18 '19 at 10:21
Technically it could also be stored in a `DATETIME2(2)`, or anything else of lower precision. When a string value is to be implicitly converted to a `DATETIME2`, the engine will pick `DATETIME2(7)` regardless of the destination, so that won't be an issue. – Jeroen Mostert Jun 18 '19 at 10:23
@JeroenMostert I agree, but the image in the post shows `datetime2(7)`, and as you said, implicit conversion would be to the default precision which is 7, so most likely it's an implicit conversion to `DateTime` – Zohar Peled Jun 18 '19 at 10:24
@ZoharPeled: yes, I meant that in addition to `DATETIME`, stored procedures/table types/TVFs/whatever not shown here could be using a `DATETIME2`, just of the wrong precision. – Jeroen Mostert Jun 18 '19 at 10:26
But then again, according to the [Data type precedence](https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql?view=sql-server-2017) page, implicit conversion will convert `DateTime` to `DateTime2`, not the other way around. So we are not looking for an SQL Server conversion here. Most likely wrong data type mappings between c# and SQL Server. – Zohar Peled Jun 18 '19 at 10:31
May my problem is when I am using this: `command.Parameters.Add(new SqlParameter(parameter.Label, parameter.Value));` and the implicit conversion is converting to DateTime. Thank you everyone! – Flavio Francisco Jun 18 '19 at 10:34
1

I'll bet that's a lesson you won't forget :-) Always specify data types for ado.net parameters! `command.Parameters.Add(parameter.Label, SqlDbType.DateTime2).Value = parameter.Value;` is the code you should use. – Zohar Peled Jun 18 '19 at 10:36
@FlavioFrancisco If you could update the question to include the problematic code then I could reopen it and update the answer to include the solution. – Zohar Peled Jun 18 '19 at 10:38
Just to complement the answer I must explicitly convert it to a DateTime instance since I am sending a type object `var dateTimeParameter = new SqlParameter(parameter.Label, SqlDbType.DateTime2) dateTimeParameter.Value = ((DateTime)parameter.Value); command.Parameters.Add(dateTimeParameter);` The snippet was from a generic method, that is why was generic than I check `if (parameter.Value is DateTime)`. Thanks a lot! – Flavio Francisco Jun 18 '19 at 12:24

How to avoid loosing precision using implicit conversion (SqlParameter) with DateTime (C#) even if I am using DateTime2 in my SQL Server?

1 Answers1

Linked