.NET DataRowComparer DateTime Comparison To T-SQL DateTime

Question

I have learned that SQL Server stores DateTime differently than the .NET Framework. This is very unfortunate in the following circumstance: Suppose I have a DataRow filled from my object properties - some of which are DateTime - and a second DataRow filled from data for that object as persisted in SQL Server:

DataRow drFromObj = new DataRow(itemArrayOfObjProps);
DataRow drFromSQL = // blah select from SQL Server

Using the DataRowComparer on these two DataRows will give an unexpected result:

// This gives false about 100% of the time because SQL Server truncated the more precise
// DateTime to the less precise SQL Server DateTime
DataRowComparer.Default.Equals(drFromObj, drFromSQL);

My question was going to be, 'How do other people deal with reality in a safe and sane manner?' I was also going to rule out converting to strings or writing my own DataRowComparer. I was going to offer that, in absence of better advice, I would change the 'set' on all of my DateTime properties to convert to a System.Data.SqlTypes.SqlDateTime and back upon storage thusly:

public Nullable<DateTime> InsertDate
{
  get
  {
    if (_InsDate.HasValue)
      return _InsDate;
    else
      return null;
  }

  set
  {
    if (!object.ReferenceEquals(null, value) && value.HasValue)
    _InsDate = (DateTime)(new System.Data.SqlTypes.SqlDateTime(value));
  }
}

I know full well that this would probably get screwed up as I used the _InsDate variable directly somewhere rather than going through the property. So my other suggestion was going to be simply using System.Data.SqlTypes.SqlDateTime for all properties where I might want a DateTime type to round trip to SQL Server (and, happily, SqlDateTime is nullable). This post changed my mind, however, and seemed to fix my immediate problem. My new question is, 'What are the caveats or real world experiences using the SQL Server datetime2(7) data type rather than the good, old datetime data type?'

Datetime2 is not supported by some third party vendors. For your issue though, in most applications it is not an issue because we don't care about milliseconds (etc). If that is the same for you, then change the value you store in your datarow to match SQL Servers precision or, you can store the TICKS of the time in SQL server instead. They are still sortable, usable in a range, and very fast since it would be an int. — Steve, Aug 08 '14 at 15:00
@Steve, while I might actually care about milliseconds, I almost certainly don't care about the nanoseconds that .NET keeps around. I don't do any real-time apps where it would be necessary. I may try the TICKS approach down the road, but I'm having enough trouble trying to keep straight the conversions from the stored UTC to user local time. — B H, Aug 08 '14 at 15:05
What is the use case here? You have some objects that did not come from SQL that you want to compare to object that come from SQL. — paparazzo, Aug 08 '14 at 16:16
@Blam: The use case is as I stated. I have an object with DateTime data and push it into the SQL Server table and read it back out. When comparing data row values, they are different. Why do I need to compare them? To get the automatically generated index value of the newly inserted row. I know there are other ways to get back a row identity, but I think my OUTPUT INSERTED.* and compare is better for a multiple row insert. By the way, I would be interested to know the who and why on the down vote. — B H, Aug 13 '14 at 10:51
There you have it. The way other people deal with it in safe an sane manner is not to retrieve Identity that way. — paparazzo, Aug 13 '14 at 12:27
@Blam: How not safe or not sane? If two newly inserted rows are identical, it doesn't matter which one gets which identity. And, if the rows are different, they will always give back the correct identity. I've read lots of advice on getting back the identity of new rows and they all require single row update or comparison of much larger data sets than my approach. I only ever work with the number of newly added records and I can let the SqlAdapter generate almost all of the code for me. (The question about down vote was not directed at you.) — B H, Aug 13 '14 at 19:33
It is not safe or sane to retrieve it based on another (non deterministic) value unless that value is guaranteed to be unique and is represented the same in both systems. Iden is going to be generated in order. Use order. — paparazzo, Aug 13 '14 at 19:36
@Blam: I don't understand what you mean by "use order". If the 'added' rows in the DataTable (actually DataTable.GetChanges()) are in a certain order, I can be guaranteed the OUTPUT INSERTED.* rows are in the same order? If so, it makes a lot more sense to do it that way, but I thought I had specifically read that 'common sense' did not apply in this case and order was not guaranteed. — B H, Aug 14 '14 at 14:36

score 2 · Answer 1 · edited May 23 '17 at 10:26

TL;DR: Comparing dates is actually hard, even though it looks easy because you get away with it most of the time.

You have to be aware of this issue and round both values yourself to the desired precision.

This is essentially the same issue as comparing floating point numbers. If two times differ by four nanoseconds, does it make sense for your application to consider them different, or the same?

For example, if two servers have logged the same event, searching for corresponding records, you wouldn't say "no that can't be the correct event because the time is wrong by 200 nanoseconds". Clocks can differ by that amount on two servers no matter how hard they try to keep their time synchronised. You might accept that an event seen on server A and logged with a time a couple of seconds after the time on server B might have been actually seen simultaneously or the other way around.

Note:

If you are comparing data which is supposed to have made some sort of round-trip out of the database, you may find it has been truncated to the second or minute. (For example if it has been through Excel or an old VB application, or been written to a file and parsed back in.)
Data originating from external sources is generally rounded to the day, the minute, or the second. (except sometimes logfiles, eventlogs or electronic dataloggers, which may have milliseconds or better)
If the data has come from SQL Server, and you are comparing it back to itself (for example to detect changes), you may not encounter any issues as they will be implicitly truncated to the same precision.
Daylight savings and timezones introduce additional problems.
If searching for dates, use a date range. And make sure you write the query in such a way that any index can be used.

Somewhat related:

Why doesn't this sql query return any results comparing floating point numbers?

As the article I linked explained in excruciating detail, I am aware that SQL Server 'datetime' is going to truncate my .NET DateTime unless I round off to the SQL Server 'datetime' precision before storing. I was just looking for some guidance on the newer 'datetime2(7)' SQL Server data type - especially any horror stories about its use - since MS didn't make that the default once it was introduced. — B H, Aug 13 '14 at 10:58
@BH "What are the caveats or real world experiences using the SQL Server datetime2(7) data type rather than the good, old datetime data type". Caveats listed in answer above. Principally you need to think hard about the notion of equality of two points in time. — Ben, Aug 13 '14 at 11:40
It was a good answer about the issues with comparing high precision values although I didn't see ceiling/floor mentioned as an aspect of rounding. I was more specifically looking for experiences - especially bad - with the datetime2(7) data type because it is somewhat new. Since this question earned me a down vote, I may withdraw it. If not, I'll probably accept your answer. — B H, Aug 13 '14 at 19:40

score 1 · Answer 2 · answered Aug 14 '14 at 15:47

Identity increments. Sort by Identity and you get insert order. You (can) control insert order.

I seriously doubt output would ever by out of order but if you don't trust it you can use @SeeMeSorted

DECLARE @SeeMeSort TABLE
( [ID] [int] IDENTITY(1,1) NOT NULL,
  [Name] [nvarchar](20) NOT NULL);
DECLARE @SeeMeSorted TABLE
( [ID] [int] primary key  NOT NULL,
  [Name] [nvarchar](20) NOT NULL);
insert into @SeeMeSort ([Name])
OUTPUT INSERTED.[ID], INSERTED.[name]
values ('fff'), ('hhh'), ('ggg');
insert into @SeeMeSort ([Name])
OUTPUT INSERTED.[ID], INSERTED.[name]
  into @SeeMeSorted
values ('xxx'), ('aaa'), ('ddd');
select * from @SeeMeSorted order by [ID];

.NET DataRowComparer DateTime Comparison To T-SQL DateTime

2 Answers2