A DateTime and time zone issue that may surprise you

It surprised me!

I was working with a third-party API, and had to specify a date range object, with a start and date. I was saving the last sync date in a database, and so had code like this…

WSCriteriaDate dateFilter = new WSCriteriaDate
{
  startDate = lastSyncDate, // pulled from the database
  endDate = DateTime.Now,
};

This all looked innocent enough. The problem was, their server was throwing intermittent exceptions when I passed this date filter object as a parameter, claiming that the start date was after the end date. I checked the data, and I was definitely sending a valid date range.

It turned out that as their server is in the EST time zone, five hours behind the UK (UTC, previously known as GMT), and their server was seeing the end date as EST time, five hours earlier than the time I had specified. Therefore, if I made a second call within five hours of the previous one, it thought I was asking for an invalid date range. We couldn’t work out why this was happening. Either both dates should be converted, or both should be left alone. There didn’t seem to be any reason to treat the two differently.

After quite a lot of backwards and forewards with their support, it turned out to be an interesting .NET issue that is probably not well known.

It turned out that the value returned by DateTime.Now includes the time zone, so when the endDate parameter arrived at their server, it recognised it as UTC, and converted it to EST. By contrast, startDate, was using a value pulled from SQL Server, which (it seems) does not include the time zone. Therefore, when their server received the end date, it assumed it was in the server’s local time zone (not having been given any information to the contrary), and did not convert it.

Therefore, to avoid the problem, we had to specify the time zone for the start date. This can be done rather painfully by using the little-known DateTimeKind parameter of the DateTime constructor…

WSCriteriaDate dateFilter = new WSCriteriaDate
{
  startDate = new DateTime(lastSyncDate.Year,
    lastSyncDate.Month,
    lastSyncDate.Day,
    lastSyncDate.Hour,
    lastSyncDate.Minute,
    lastSyncDate.Second,
    DateTimeKind.Local),
  endDate = DateTime.Now.AddDays(1),
};

This worked fine.

As it happens, due to the nature of the data we were requesting, it turned out to be much easier to add six hours to the end date, as this would avoid the issue, without causing us any problems. It’s also worth noting that you can convert UTC DateTime to a local time zone using the DateTime.ToLocalTime() method, which is also much cleaner than the code shown above.

It’s worth noting that we discovered the problem by using Fiddler. I made a call to their API, and then looked in Fiddler to see what the request actually looked like…

If you look at the start date, it is 2018-11-20T16:00:00, ie without a time zone. Therefore, the server was assuming that this was its own local time zone, ie EST.

By contrast, the end date includes a time zone, 2018-11-20T17:20:40.8877812+00:00, with the bold part indicating that this time is UTC+00:00. The server understood this and converted the date to its time zone, which is 12:20:40 in UTC-05:00.

In short, DateTime.Now captures the current computer time in the local machine time zone, whereas it seems that the time stored in SQL Server does not contain the time zone information.

Be First to Comment

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.