1

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.

Akash Kava
  • 39,066
  • 20
  • 121
  • 167
  • Have you seen the SqlDateTime class? https://learn.microsoft.com/en-us/dotnet/api/system.data.sqltypes.sqldatetime?view=net-7.0 might be what your looking for – JDChris100 Feb 19 '23 at 14:25
  • SQL Server and c# are both Microsoft Products and use same time format which is a double. Both use a TICK which is 100nsec. and time zero is 1/1/01. It is not Unix where Unix time zero is 1/1/1970. – jdweng Feb 19 '23 at 14:33
  • 1
    I can't speak to what's going on in your EF world but there is no loss of precision if you save `dt1.DataTime` or `dt2.DataTime` to a datetime2 column. You could also use a SQL Server `datetimeoffset` column to preserve the offset. – Dan Guzman Feb 19 '23 at 14:35
  • @JDChris100 Yes I have, I even tried converting DateTime to and from SqlDateTime, still I can't get exact date represented in Sql Server. – Akash Kava Feb 19 '23 at 14:36
  • 3
    @jdweng: "and use same time format which is a double" - no, it's a 64 bit *integer* number of ticks, at least in .NET. (SQL Server may do something different, of course.) – Jon Skeet Feb 19 '23 at 14:37
  • @DanGuzman I tried, its same. – Akash Kava Feb 19 '23 at 14:38
  • @JonSkeet In .Net it is 64 bit long, but in SQL server, last part of ticks 510000 and 50000 both got converted to 506667 which is only possible through conversion to double and back. – Akash Kava Feb 19 '23 at 14:44
  • 1
    @AkashKava: Yes, I was correcting jweng's assertion. (I don't actually know what the internal SQL Server representation is. It would be odd to use a floating point number, particularly when the documentation *claims* that it supports 7-digit subsecond precision, but I've definitely seen stranger things...) – Jon Skeet Feb 19 '23 at 14:46
  • 1
    The 5066667 value looks suspiciously like there is a conversion from a SQL datetime to SQL datetime2 involved somewhere. A SQL datetime has 1/300 second precision will result in the value you see. To wit: `DECLARE @datetime datetime = '2023-02-19 13:39:36.507';SELECT CAST(@datetime AS datetime2);` – Dan Guzman Feb 19 '23 at 14:47
  • Quite old but here's a blog post by Ronen Ariely about how SQL Server stores [`DateTime2` values.](https://ariely.info/Blog/tabid/83/EntryId/162/Examine-how-DateTime2-type-stored-in-the-data-file.aspx), FWIW – Zohar Peled Feb 19 '23 at 15:47
  • @ZoharPeled Thanks, but the post doesn't how the actual value is stored. Like in most cases, in Java, JavaScript and in Unix, date is stored in milliseconds from EPOCH, in .NET it is stored in ticks which is million'th of a second. – Akash Kava Feb 20 '23 at 04:49
  • @DanGuzman I am using datetime2 and still it is doing this. – Akash Kava Mar 15 '23 at 09:24
  • @AkashKava, I have not been able to reproduce the issue sans EF. Let's [continue this discussion in chat](https://chat.stackoverflow.com/rooms/252535/how-to-format-c-datetimeoffset-exactly-same-as-in-sql-servers-datetime27-form). – Dan Guzman Mar 15 '23 at 13:29

0 Answers0