.NET DateTime, another resolution when converting to OADate and vice versa?

Question

I am converting DateTime to OADate. I expected to get the same DateTime when converting OADate back, but now it has only millisecond resolution and therefore is different.

var a = DateTime.UtcNow; double oadate = a.ToOADate(); var b = DateTime.FromOADate(oadate); int compare = DateTime.Compare(a, b); //Compare is not 0; the date times are not the same 

Pliers from a: 634202170964319073

Pliers from b: 634202170964310000

OADate double: 40437.290467951389

What is the reason for this? DateTime resolution is clearly good enough.

Answer 1

The static method called by ToOADate clearly divides the ticks by 10000, and then stores the result in long, thus removing any auxiliary millisecond

Does anyone know where to find OADate format specifications?

  private static double TicksToOADate(long value) { if (value == 0L) { return 0.0; } if (value < 0xc92a69c000L) { value += 0x85103c0cb83c000L; } if (value < 0x6efdddaec64000L) { throw new OverflowException(Environment.GetResourceString("Arg_OleAutDateInvalid")); } long num = (value - 0x85103c0cb83c000L) / 0x2710L; if (num < 0L) { long num2 = num % 0x5265c00L; if (num2 != 0L) { num -= (0x5265c00L + num2) * 2L; } } return (((double)num) / 86400000.0); } 

Answer 2

I think this is a great question. (I just opened it.)

If you work with dates close to 1900, DateTime will have higher accuracy than the OA date. But for some unclear reason, the authors of DateTime struct just love are truncated to the nearest whole millisecond when they convert between DateTime and something else. Needless to say, this makes very accurate accuracy for no good reason.

Here's a workaround:

 static readonly DateTime oaEpoch = new DateTime(1899, 12, 30); public static DateTime FromOADatePrecise(double d) { if (!(d >= 0)) throw new ArgumentOutOfRangeException(); // NaN or negative d not supported return oaEpoch + TimeSpan.FromTicks(Convert.ToInt64(d * TimeSpan.TicksPerDay)): } public static double ToOADatePrecise(this DateTime dt) { if (dt < oaEpoch) throw new ArgumentOutOfRangeException(); return Convert.ToDouble((dt - oaEpoch).Ticks) / TimeSpan.TicksPerDay; } 

Now consider (from your question) the DateTime given:

 var ourDT = new DateTime(634202170964319073); // .ToSting("O") gives 2010-09-16T06:58:16.4319073 

The accuracy of any DateTime is 0.1 μs.

Next to the date and time we are considering, OA date accuracy:

Math.Pow(2.0, -37.0) days or around 0.6286 μs

We conclude that in this area a DateTime is six times more accurate than the OA date (a bit later).

Translate ourDT to double using my extension method above

 double ourOADate = ourDT.ToOADatePrecise(); // .ToString("G") gives 40437.2904679619 // .ToString("R") gives 40437.290467961888 

Now, if you convert ourOADate back to DateTime using the FromOADatePrecise static method above, you get

2010-09-16T06:58:16.4319072 (recorded in the format "O" )

Comparing with the original, we see that the loss of accuracy in this case is 0.1 μs. We expect that the accuracy loss will be within ± 0.4 μs, since this interval has a length of 0.8 μs, which is comparable to the previously mentioned 0.6286 μs.

If we go in the opposite direction, starting with a double representing the OA date, not too close to 1900, and first use FromOADatePrecise and then ToOADatePrecise , then go back to double , but because the accuracy of the intermediate DateTime exceeds the accuracy of the OA date, we expect perfect round trip in this case. If, on the other hand, you use the BCL FromOADate and ToOADate in the same order, it is extremely unlikely to get a good circuit (if only the double that we started with has a special shape).

Answer 3

Maybe something has to do with double precision, not DateTime.