2

Background

The field TimestampCreation is created as such in the DB:

[TimestampCreation] [datetime2](7) NOT NULL

If I add the field to a datatable like this:

table.Columns.Add(new DataColumn("TimestampCreation", typeof(DateTime)));

the value loses precision, because it's stored like so:

2016-12-07 11:38:39.4990000

When it should be 

2016-12-07 11:38:39.4998426

One solution I tried

If I add the column like so:

table.Columns.Add(new DataColumn("TimestampCreation", typeof(string))); // Actually datetime2

The value is stored correctly to DB, but this feels dirty/hacky.

Usage in code

The value is set like this:

public static void AddToTable(this EntityDto source, DataTable table)
{
    var row = table.NewRow();

    ...
    ...
    row["TimestampLastModification"] = source.TimestampLastModification;

    table.Rows.Add(row);
}

Where source.TimestampLastModification is of type DateTime:

public DateTime TimestampLastModification { get; set; }

Next the datatable is saved to storage like this:

Root.PluginManager().PersistentStorage(ParametersHelper.OverallSchema).Insert(ref table)

Question

What's the proper way to add a datetime2 field to a datacolumn and keep the expected precision?

Spikee
  • 3,967
  • 7
  • 35
  • 68
  • 1
    Where's the code that transfers the data from the `DataTable` to the database? That's where things get messed up and `DATETIME` is used as the type rather than `DATETIME2`. `System.DateTime` itself has enough precision and `DataColumn` just stores the value as-is. – Jeroen Mostert Dec 07 '16 at 10:53
  • Okay, I'll update. – Spikee Dec 07 '16 at 10:54
  • This is still missing the code that *writes* to the database (or reads it into `EntityDto`, depending on your scenario). `table` is just an in-memory representation and is not your problem. – Jeroen Mostert Dec 07 '16 at 10:59
  • @JeroenMostert: It is a _write_, I've added the insert code as well. It's a legacy provider that works on datasets/datatables. – Spikee Dec 07 '16 at 11:03
  • 1
    And there you have it. Whatever the implementation of `Root.PluginManager().PersistentStorage().Insert()` is, it is the thing deciding that all `DateTime` columns should be transferred as `DATETIME` and not `DATETIME2`. If you can't influence that, you're out of luck. `DataColumn` has no way to communicate provider-specific types like `DATETIME2` explicitly (and even if it did, your provider probably has no support). You could try passing in a `DateTimeOffset`, but I doubt it has support for that. If you must pass a string, make sure it's ISO (`.ToString("yyyyMMdd HH:mm:ss.fffffff")`). – Jeroen Mostert Dec 07 '16 at 11:08

1 Answers1

0

I found a solution, it was something at my own end, not the third party dependency. I fixed it by casting the date to nvarchar within the query:

SELECT CONVERT(VARCHAR, [TimestampLastModification], 126) [TimestampLastModification]
FROM ...

Next I convert the string to a datetime by code:

this.TimestampLastModification = Convert.ToDateTime(source["TimestampLastModification"].ToString()),

It's strange that casting an object, that is actually of type DateTime, to DateTime causes precision loss, but the above does work.

Spikee
  • 3,967
  • 7
  • 35
  • 68
  • 1
    What you're doing here is converting the T-SQL type `DATETIME2` to a string, and then converting that to a .NET `System.DateTime`. This works. What didn't work is reading the T-SQL type `DATETIME2` as the type `DATETIME` (which is what your provider is doing). `DATETIME` != `System.DateTime`. The precision loss is caused by the `DATETIME` T-SQL type having a crappy precision to begin with, not because you're casting objects. `DATETIME2`, on the other hand, was created specifically to align with `System.DateTime` (but requires support since it's not a standard type). – Jeroen Mostert Dec 08 '16 at 17:27