DateTime output issue in Microsoft 365 - Outlook

[AdamTrevett]

Hi All,

I have a process that pulls email from a shared mailbox. When the list is returned from MS Grapgh API response, i get the response as createdDateTime":"2022-11-29T18:54:00Z" and when the JOSN code stage runs, the output collection has the field createdDateTime with value "11/29/2022 11:54:00 PM". This is misleading as it is trying to convert an existing UTC time value to a UTC time based on the current time zone my machine is on.

I did some digging and seems that NewtonSoft JSON is causing this issue.

Anyone else are having this issue? how do i overcome it as my resource PCs are in multiple location [1-EST, 2-PST] and i do not wish to hard code the timespan.

[ewilson]

Hi @AdamTrevett,

There's one way to handle this with Newtonsoft. You can create a ​JsonSerializerSettings instance and set the value of the DateParseHandling property to None. This will result in DateTime values not be processed by Newtonsoft. Instead, they will passed through as strings.

To do this, go to the Global Code section of the VBO and search for the function titled ConvertToDataTable. You want to change the code within that function from this:

public DataTable ConvertToDataTable(string json)
{
	object o = JsonConvert.DeserializeObject(json);
	return (DataTable)DeserialiseGeneric(o, true);
}​

 
to this:

public DataTable ConvertToDataTable(string json)
{
	JsonSerializerSettings serializerSettings = new JsonSerializerSettings();
	serializerSettings.DateParseHandling = DateParseHandling.None;

	object o = JsonConvert.DeserializeObject(json, serializerSettings);
	return (DataTable)DeserialiseGeneric(o, true);
}

Once you're done, the Global Code should look like this:

Keep in mind that if you are performing any date/time calculations you will now have to handle changing the raw string to an actual DateTime data item.

Cheers,
Eric