Hello All,

Recently, in an application where we were saving the .NET DateTime value in SQL database, we had to compare this DateTime later in the .NET application. We realized that even the same DateTime stamp was not equating once it got fetched from database. I got Once we investigated further we found that the time stamp that we were sending to save in database, was not saving with the complete value and there were some more issues. It looks common requirement where you saving a value in database and later comparing it to add some business logic.  So I am sharing here my findings.

# 1

SQL DateTime type only stores time till milliseconds (3 digits) while in .NET it is stored till ticks. One millisecond is equivalent to 10,000 ticks. It means if you construct the .NET DateTime object from the DateTime of database, then it will never match because it will have all zero after the milliseconds. Lets see an example.
Here I am showing the same .NET DateTime value at application, in database table and again .NET DateTime read from the DB.

.NET DateTime

{6/19/2017 9:24:14 PM}
      Date: {6/19/2017 12:00:00 AM}
      Day: 19
      DayOfWeek: Monday
      DayOfYear: 170
      Hour: 21
      Kind: Local
      Millisecond: 777
      Minute: 24
      Month: 6
      Second: 14
      Ticks: 636335042547778146
      TimeOfDay: {21:24:14.7778146}
      Year: 2017

Let’s see how it is saved in database.

SQL DateTime

In database table it looks like as

When we fetch from database and convert it into it returns the following object
{6/19/2017 9:24:14 PM}
      Date: {6/19/2017 12:00:00 AM}
      Day: 19
      DayOfWeek: Monday
      DayOfYear: 170
      Hour: 21
      Kind: Unspecified
      Millisecond: 777
      Minute: 24
      Month: 6
      Second: 14
      Ticks: 636335042547770000
      TimeOfDay: {21:24:14.7770000}
      Year: 2017

Here we see the TimeOfDay component in both the object then we find that while in first object we have 7778146 while in second 7770000, it means last 4 digits are chopped off and reset to 0.
So is it safe if we compare till milliseconds only (say by using ToString(“MM/dd/yyyy hh:mm:ss.fff”))?
NO
There are some more mystery to it. Let’s see in second point

# 2

Here, I saved a new record in the database and the .net DateTime as
{6/19/2017 11:49:55 PM}
      Date: {6/19/2017 12:00:00 AM}
      Day: 19
      DayOfWeek: Monday
      DayOfYear: 170
      Hour: 23
      Kind: Local
      Millisecond: 731
      Minute: 49
      Month: 6
      Second: 55
      Ticks: 636335129957315136
      TimeOfDay: {23:49:55.7315136}
      Year: 2017

and when we see the record in the table, we see

Oh.. here we see the 730 millisecond while in .NET object it was 731. Let’s fetch the same and check the .net object as

{6/19/2017 11:49:55 PM}
      Date: {6/19/2017 12:00:00 AM}
      Day: 19
      DayOfWeek: Monday
      DayOfYear: 170
      Hour: 23
      Kind: Unspecified
      Millisecond: 730
      Minute: 49
      Month: 6
      Second: 55
      Ticks: 636335129957300000
      TimeOfDay: {23:49:55.7300000}
      Year: 2017

Here again we see 730 millisecond. It means we are losing one millisecond. It also suggests that comparing the two values till milliseconds would also not work. But why this is happening. Let’s dig it more.

Actually SQL DateTime stores till 1/3 millisecond approximately so the last digit of millisecond would always be

**0
**3
**7
**0

and SQL rounds of the milliseconds passed to it so if you pass
001 turns to 000
002 turns to 003
004 turns to 004
005 turns to 007
…
009 turns to 010

So can we just chop off milliseconds and compare the DateTime?
NO

It may work most of the time. But there are few chances to fail. Say we have a time as 09.00.00.999 then it will turn to 09.00.01.000 so here second got increased by 1. Even minute/hour can be changed if similar situation occur. So here we just have the option to round off the time based on the above logic and then compare.

Do we have any other option?
Yes

DateTime2

To overcome this, there is a new SQL data type datetime2 was introduced in SQL server 2008 which got the ability to save the millisecond till 7th precision. It is like an extension of DateTime which saves the time more accurately. Lets have a look on that

So we see here that the time is saved as 2017-06-19 23:49:55.7353342 while the same was saved in DateTime (type) as 2017-06-19 23:49:55.737 (rounded off). If we now fetch the same and assign the .NET DateTime object we get the exact date time and equality works as expected.

Note – There is one more significant update in datetime2. In DateTime if we want to same a default minimum DateTime then it was 1753 but in DateTime it could be 0001.

Conclusion

In this post, we discussed the behavior of SQL DateTime object, its issues and possible workarounds. Then we saw that the how the issues got resolved in datetime2 which was introduced in SQL server 2008. Obviously, it takes more space in database as it saves the time more granular level. I have rarely seen that DateTime values are stored in datetime2 format, mostly in DateTime at least in legacy application. And many of us don’t know the exact difference or may face the issues that we discussed. If we are working on some legacy application then we may not able to change the SQL data type, in that scenario, putting the round off logic could work.

Hope you have enjoyed the post. Do share your valuable feedback.

Happy Coding,
Brij