By Steve Endow
Prior to this year, I rarely had to deal with time zones when developing integrations for Dynamics GP.
The customer was typically using GP in a US time zone, the SQL Server was on premise in that time zone, and all of their data usually related to that same time zone. Nice and simple.
Dynamics GP then introduced the DEX_ROW_TS field to several tables, and I would regularly forget that field used a UTC timestamp. That was relatively minor and easy to work around.
But with the increasing popularity of Software As A Service (SaaS) platforms, I'm seeing more and more data that includes UTC timestamps. I didn't think too much about this until today, when I found an issue with how a SaaS platform provided transaction dates in their export files.
Here is a sample data from a file that contains AP Invoices:
2017-09-05T14:26:05Z
This is a typical date time value, provided in what I generically call "Zulu time" format. Apparently this format is defined in
ISO 8601.
The format includes date and time, separated by the letter T, with a Z at the end, indicating that the time is based on the UTC time zone.
So why do we care?
Until today, I didn't think much of it, as my C# .NET code converts the full date time string to a DateTime value based on the local time zone, something like this:
string docDate = header["invoice-date"].ToString().Trim();
DateTime invoiceDate;
success = DateTime.TryParse(docDate, out invoiceDate);
if (!success)
{
Log.Write("Failed to parse date for invoice " + docNumber + ": " + docDate, true);
}
This seemed to work fine.
But after a few weeks of using this integration, the customer noticed that a few invoices appeared to have the incorrect date. So an 8/1/2017 invoice would be dated 7/31/2017. Weird.
Looking at the data this morning, I noticed this in the SaaS data file for the Invoice Date field:
2017-08-25T06:00:00Z
2017-08-21T06:00:00Z
2017-08-23T06:00:00Z
Do you see the problem?
The SaaS vendor is taking the invoice date that the user in Colorado enters, and is simply appending "T06:00:00Z" to the end of all of the invoice dates.
Why is that a problem?
Well, when a user in Colorado enters an invoice dated 8/25/2017, they want the invoice date to be 8/25/2017 (UTC-7 time zone). When the SaaS vendor adds an arbitrary time stamp of 6am UTC time, my GP integration will dutifully convert that date into 8/24/2017 11pm Colorado time.
For invoices dated 8/25, that may not matter too much, but if the invoice is dated 9/1/2017, the date will get converted to 8/31/2017 and post to the wrong fiscal period.
To make things even more fun, I found that the SaaS vendor is also storing other dates in local time.
2017-09-05T08:24:36-07:00
2017-09-05T08:26:22-07:00
2017-09-05T08:28:13-07:00
So I have to be careful about which dates I convert from UTC to local time, and which ones I truncate the time to just get the date, and which ones are local time. In theory, the .NET date parsing should handle the conversion properly, assuming the time zone is correct, but I now know that I have to keep an eye on the vendor data.
I will be contacting the vendor to have them fix the issue with the invoice dates--there is no good reason why they should be appending "T06:00:00Z" to dates.
Expect to see a lot more of this date format and related date issues as more customers adopt cloud-based solutions and services.
