Datetime mismatch problems in Access database

Question

I'm trying to store a datetime in an Access database using C#. I'm writing the app in Visual Studio 2015. When I read the value out of the Access table it doesn't exactly match the value that I stored in there. The number of ticks is different. I've seen some posts about the differences between .NET System.DateTime and SQL server DateTimes. It appears that for SQL server I should use a SqlDateTime or a datetime2. In my case I'm using a legacy .mdb Access database file. I might be able to upgrade to an accdb file.

Answer 1

I know this may all be totally obvious, but assuming it's not a timezone difference issue, as Dai suggested in the comments, my educated guess is that it's something you both touched on -- the granularity supported by the Access data type you're using for your date/time column.

Depending on what data type you use (in any database), there may be a difference in the granularity and/or translation between the stored value and the .NET ticks value that can cause relatively small differences between what is stored and what is retrieved.

If you only need to store the most accurate date possible (i.e. don't need to perform operations on that column in the DB), and have to use a legacy .mdb Access database (or any database that doesn't offer a data type with perfect fidelity for the .NET DateTime type), then I think you have two options:

1. Find the best numeric data type (ideally an unsigned 64-bit integer type) offered by the database and use that to manually store the ticks count, and handle any loss of resolution. Even if it's not perfect, you will likely get higher granularity from one of the DB's wider numeric types than you might from its native "date/time" type. Of course, as Dai mentioned, you should also store the timezone/offset information along with the tick count.

2. Store either the tick count or the text representation of the date and time (again, including timezone/offset) as a string, and parse it when you read it from the database. This obviously has big downsides -- besides not being able to perform operations on the values in the DB, it's an order of magnitude slower than other methods -- but it does give you perfect resolution if that is a requirement.

Answer 2

There will only be a difference if you try to store ticks representing microseconds and nanoseconds as these may be beyond the resolution of data type Date which basically is a double. For extended values the resolution of this is one millisecond only.

Thus, you should round or chop the small ticks. This can be done with method ToOADate() as demonstrated here:

DateTime dateTime = DateTime.Now;
double oleTime = dateTime.ToOADate();
DateTime convertedTime = DateTime.FromOADate(oleTime);

Console.WriteLine(dateTime.Ticks.ToString());
Console.WriteLine(oleTime.ToString());
Console.WriteLine(convertedTime.Ticks.ToString());

The result is like:

636170456284955745
42715.3984779514
636170456284950000

It can also be illustrated by the converted ticks of some VBA Date values including the extreme:

'    100-01-01 00:00:00.000 ->   31241376000000000
'    100-01-01 00:00:00.001 ->   31241376000010000
'    100-01-01 00:00:00.002 ->   31241376000020000
'   1899-12-30 00:00:00.000 ->  599264352000000000
'   2018-08-18 03:24:47.000 ->  636701594870000000
'   2018-08-18 18:24:47.000 ->  636702134870000000
'   9999-12-31 23:59:59.000 -> 3155378975990000000
'   9999-12-31 23:59:59.998 -> 3155378975999980000
'   9999-12-31 23:59:59.999 -> 3155378975999990000

Note please, that Office and VBA normally ignores milliseconds but actually is perfectly capable of holding these.