How to format C# DateTimeOffset exactly same as in SQL Server's DateTime2(7) format?

324 Views Asked by At

Saving following two DateTime values in SQL Server's DateTime2(7) column results in same DateTime2 value.

var dt1 = new DateTimeOffset(638124107765100000, TimeSpan.Zero);
var dt2 = new DateTimeOffset(638124107765000000, TimeSpan.Zero);

Value in Sql Server's DateTime2(7) column.

2023-02-19 13:39:36.5066667

This value goes through Entity Framework. Now I can see that SQL Server uses some different form of storage to store value, so the value gets changed when we read it back.

Now I have no problem with little lost precision but I want to format DateTime in such a way that the text should match exactly what is stored in SQL Server.

The only way currently I can do is by ignoring whole milliseconds above 10.

I have tried the following:

private static DateTimeOffset ToSqlServerPrecision(DateTimeOffset dt) {
    var n = Math.Round((double)dt.ToUnixTimeMilliseconds() / (double)1000, 1);
    n = n * 1000;
    return DateTimeOffset.FromUnixTimeMilliseconds((long)n);
}

ToSqlServerPrecision(dt1).ToString("yyyy-MM-dd HH:mm:ss.fffff")

Both result in:

2023-02-19 13:39:36.50000
2023-02-19 13:39:36.50000

But I doubt, this will might fix all edge cases.

I have also tried following,

private static DateTimeOffset ToSqlServerPrecision(DateTimeOffset dt) {
    var p = (double)1/3; // or 3.33 
    var n = Math.Round((double)dt.UtcTicks / p, 1);
    n = n * p;
    return new DateTimeOffset((long)n, TimeSpan.Zero);
}

Both result in:

2023-02-19 13:39:36.51000
2023-02-19 13:39:36.49999

I guess Ticks to SQL Server compatible time storage isn't simply casting milliseconds/ticks to double conversion.

All I want is to get exact same text representation that is stored in SQL Server.

0

There are 0 best solutions below