I have a power app which has a form connected to a Azure SQL server.
I've issues with Times being saved incorrectly when added after UK daylight savings
This means that an entry input for 21/04/19 at 22:15 is appearing in the SQL database as 21:15
When I use the App to view the data however it shows correctly as 22:15 even though the SQL date is different.
E.g.
ID StartDate FinishDate
First Question is: How the app is showing correct even thought the Database is wrong?
All I am doing in the App is ThisItem.StartDate
Second Question is: How can I stop this from happening as there are Flows which are running from the data sending emails and these are going out wrong.
I can see from this post the issue is due to the way the form saves data, by adding the time to the date. https://powerusers.microsoft.com/t5/General-Discussion/Daylight-savings-time-problem-one-hour-off/m-...
DateValue4_3.SelectedDate + Time(Value(HourValue4_3.Selected.Value), Value(MinuteValue4_3.Selected.Value), 0)
This fix doesn't seem to work for me.
As a test a Brand new App with an unmodified Form connected to the SQL source:
Still gives the following issues in the Database.
StartDate FinishDate
Is there a fix in the works for this as I can't have this issue every year and can't be expected to change every standard form with a date to fix this.
Thanks,
Nicky
Solved! Go to Solution.
Hi @OneThing ,
Based on the issue that you mentioned, I think this issue is related to Time Zone issue.
It is an known issue that, when you use datetime value in SQL with PowerApps, then you may have the offset issues. Please check the following blog for details:
https://powerapps.microsoft.com/en-us/blog/working-with-datetime-values-in-sql/
As an alternative solution, you could consider take a try to add a datetimeoffset type column in your SQL Table to store the date time value instead of datetime value.
In addition, I think the TimeZoneOffset function, could also achieve your needs. Please check the alternative solution mentioned within above blog.
Currently, within PowerApps, I think there is no simple/other solution to fix this issue except above alternative solution.
If you want this issue to fixed within PowerApps and SQL Server, please consider submit an idea to PowerApps Ideas Fourm:
https://powerusers.microsoft.com/t5/PowerApps-Ideas/idb-p/PowerAppsIdeas
Best regards,
I recommend this article by @CarlosFigueira for a good explanation. I recently had same problem. When I created schema I should have used column type datetimeoffset.
Hi @mogulman
Thanks for the reply. This was a useful post. I've never seen before.
I've done a quick test on the database and having a datetimeoffset date still shows 2019-04-02 08:00:00 +00:00 When I selected 09:00 in the form. The offset is correct as GMT and UTC are currently the same.
Does this mean i'm going to have to put a timezoneoffset into every calculation when saving and reading dates?
Thats going to be a major problem for app creation going forward then.
Hi @OneThing ,
Based on the issue that you mentioned, I think this issue is related to Time Zone issue.
It is an known issue that, when you use datetime value in SQL with PowerApps, then you may have the offset issues. Please check the following blog for details:
https://powerapps.microsoft.com/en-us/blog/working-with-datetime-values-in-sql/
As an alternative solution, you could consider take a try to add a datetimeoffset type column in your SQL Table to store the date time value instead of datetime value.
In addition, I think the TimeZoneOffset function, could also achieve your needs. Please check the alternative solution mentioned within above blog.
Currently, within PowerApps, I think there is no simple/other solution to fix this issue except above alternative solution.
If you want this issue to fixed within PowerApps and SQL Server, please consider submit an idea to PowerApps Ideas Fourm:
https://powerusers.microsoft.com/t5/PowerApps-Ideas/idb-p/PowerAppsIdeas
Best regards,
User | Count |
---|---|
133 | |
132 | |
97 | |
75 | |
74 |
User | Count |
---|---|
206 | |
196 | |
70 | |
60 | |
52 |