cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
WAstarita
Level: Powered On

Date read from SQL Server is off by 4 hours

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?

1 ACCEPTED SOLUTION

Accepted Solutions
PowerApps Staff AndyPennell
PowerApps Staff

Re: Date read from SQL Server is off by 4 hours

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.

2 REPLIES 2
PowerApps Staff AndyPennell
PowerApps Staff

Re: Date read from SQL Server is off by 4 hours

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).

 

PowerApps Staff AndyPennell
PowerApps Staff

Re: Date read from SQL Server is off by 4 hours

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.

Helpful resources

Announcements
firstImage

PowerApps Monthly Community Call!

Join us next Wednesday for our Demo Extravaganza, October 16, 2019 8am PDT.

firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Top Kudoed Authors
Users Online
Currently online: 430 members 4,547 guests
Please welcome our newest community members: