Date read from SQL Server is off by 4 hours

[WAstarita]

I've read extensively through the forum and I know this has been an issue in different contexts but mine is similar yet unique.

I'm reading from a 2012 SQL Server, which is the backend for my clients CRM software (Goldmine).

The their wisdom, the calendar activites have an ONDATE field that stores the dates as datetime fields in UTC format

2016-08-31 00:00:00:000.  There is a second field ONTIME that stores the time as a string.  No idea why but it makes the dates consistent where no date fields have its time set to 0.

All my dates are off 1 Day.  When I look at the field without any formating in Powerapps studio, it comes back four hours behind so the date I used above is coming back 8/31/2016 8:00PM.  It doesn't matter what device or platform I view the app on (I've tested on Android, IOS and Chrome/Edge), I get the same issue.  All my testing devices including the SQL server are in NY so they are set to Eastern Standard Time.    I am not using the Datepicker field, only reading from the database into Text Boxes.  There are several different tables with date fields of the same format and all are giving the same issue.

Any way around this other than remembering to add 4 hours to everything?

[AndyPennell]

I tried a label with this formula:

DateTimeValue("2016-08-31 00:00:00Z")

it it showed the date/time in my local time (PST). Yyou need to add a "Z" on the end of your UTC string before converting to a date, and remove the millseconds as we seem unable to parse that.

If the Z is absent then the time is parsed as if it is in the current timezone. (This is a part of the UTC spec).

[AndyPennell · Accepted solution]

Actually on closer reading of the spec, millseconds are separated with a period, not a colon, and PowerApps supports that just fine.

So, in summary, your UTC date string is not ISO compliant: it lacks a Z (indicating no time zone) and uses the wrong milliseconds separator. As you can probably not fix your server to give you the data in the correct format, you will have to use the string value and fix it up, then pass the fixed version to DatetimeValue.