Before this year, I rarely encountered time zone issues when creating integrations for Dynamics GP.

Usually, the customer used GP in a US time zone, with the SQL Server located on-site within the same time zone. All their data typically corresponded to that time zone, making things straightforward.

Dynamics GP introduced the DEX_ROW_TS field to several tables, which I often forgot used a UTC timestamp. This was a relatively small issue and easy to resolve.

However, with the rising use of Software as a Service (SaaS) platforms, I’m seeing more data using UTC timestamps. This didn’t concern me much until recently when I encountered a problem with how a SaaS platform provided transaction dates in their export files.

Here’s an example of data from a file containing AP Invoices:

    2023-08-27T18:05:05Z

This is a standard date and time value, formatted in what is commonly known as “Zulu time” and defined by ISO 8601.

This format presents the date and time, divided by the letter “T,” and ends with a “Z,” signifying that the time is based on the UTC time zone.

So, why is this important?

Until recently, I didn’t give it much thought. My C# .NET code converts the complete date and time string to a DateTime value based on the local time zone, as shown below:

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 function correctly.

However, after several weeks of using this integration, the customer observed that a few invoices displayed incorrect dates. For example, an invoice dated August 1, 2017, would show as July 31, 2017. This was strange.

Examining the data this morning, I spotted the following in the SaaS data file for the Invoice Date field:

2024-06-27T20:53:09Z
2022-10-18T23:58:54Z
2023-03-07T02:10:53Z

Notice the issue?

The SaaS vendor is taking the invoice date entered by the user in Colorado and appending “T06:00:00Z” to the end of each invoice date.

Why is this problematic?

When a user in Colorado enters an invoice dated August 25, 2017, they expect it to be recorded as August 25, 2017 (UTC-7 time zone). When the SaaS vendor adds an arbitrary timestamp of 6 am UTC, my GP integration converts that date to August 24, 2017, 11 pm Colorado time.

For invoices dated August 25th, this might not be a significant issue. But if the invoice is dated September 1, 2017, it will be converted to August 31, 2017, posting to the incorrect fiscal period.

To complicate matters, I discovered that the SaaS vendor also stores other dates in local time.

2024-06-07T03:29:08Z
2023-09-20T18:26:04Z
2023-08-13T23:50:06Z

Consequently, I need to differentiate which dates to convert from UTC to local time, which dates to truncate to just the date, and which are already in local time. Although the .NET date parsing should manage the conversion correctly if the time zone is accurate, I am now aware that I need to monitor the vendor data.

I will contact the vendor to correct the problem with the invoice dates. There’s no valid reason to append “T06:00:00Z” to dates.

Expect to encounter this date format and related date issues more frequently as more companies embrace cloud-based solutions and services.

Steve Endow is a Microsoft MVP in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Twitter, YouTube, and Google+

http://www.precipioservices.com