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.

View solution in original post

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.

View solution in original post

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

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

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (5,451)