#TIL converting a JSON date from a Dynamics 365 Business Event in a Logic App

While building a Logic App to perform actions based on business events in Dynamics 365, I faced a slight challenge – one of those challenges can sometimes be tricky in Logic Apps but are very very easy to achieve in a coded solution.

The issue

The Logic App in question was triggered by a business event in Dynamics 365 related to a purchase order. The trigger provided data, including the purchase order date, which needed to be used in a subsequent web service call. Here’s a snippet of the data from the Logic App trigger:

{
    ...
    "EventTime": "/Date(1675857180000)/",
    "EventTimeIso8601": "2023-02-08T11:53:00.3483162Z",
    ...
    "PurchaseOrderDate": "/Date(1675814400000)/"
    ...
}

The PurchaseOrderDate was the desired field, but it was in an unfamiliar format. After searching, I discovered it was a format used by Microsoft in JSON payloads, representing the date as the number of milliseconds since 1970 (Credit to this blog : Converting JSON date string /Date()/ to Date object – Davidsekar.com).

To perform the conversion, we need the integer value of the attribute and add that number of millseconds to the date time of January 1st 1970.

The coded solution

The blog post provided a simple solution for performing the transformation in .NET code, but that approach isn’t readily available in Logic Apps.

To perform the conversion using code, you would need to create a Function App that the Logic App can utilise. However that involves significant overhead and I was hoping for a native solution within the Logic App..

The Logic App solution

The native solution in Logic Apps, utilising expressions, is quite simple. Despite the complex appearance of the expression, in the steps that follow we’ll build up the expression in steps so it is more clear how it works.

addSeconds('1970-01-01T00:00:00Z', div(int(replace(replace(triggerBody()?['PurchaseOrderDate'],'/Date(',''),')/','')), 1000), 'yyyy-MM-dd')

Step 1 – Get just the number from the purchase order date

The first step is to get from the JSON formatted date just the number, this is done by:

1. replacing all the leading and trailing text in the string with nothing.
2. use the int function to convert the number string to an integer

int(replace(replace(triggerBody()?['PurchaseOrderDate'],'/Date(',''),')/',''))

Step 2 – Convert it from milliseconds to seconds

Next we use the div function to divide the number of milliseconds by 1000, to get the number of seconds since 1970.

div(int(replace(replace(triggerBody()?['PurchaseOrderDate'],'/Date(',''),')/','')), 1000)

Step 3 – Add the number of seconds to 1 Jan 1970

Finally we can then add the number of seconds to January 1st 1970. In this example I’m also specifying the date format that the web service I’m calling expects the date to be in (e.g. 2023-02-08).

addSeconds('1970-01-01T00:00:00Z', div(int(replace(replace(triggerBody()?['PurchaseOrderDate'],'/Date(',''),')/','')), 1000), 'yyyy-MM-dd')