cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Meneghino
Level 10

Please help with UTC

How can we set a date to be in UTC?

 

Text(Date(2017,1,1), UTC) gives on my system "2016-12-31T23:00:00.000Z"

 

This is why writing a date to a database often goes wrong by a day.

 

How can I obtain the value "2017-01-01T00:00:00.000Z" to be able to write to a database?

 

Obviously you can do it with text, but any other better way?

 

PS Here is how to do it with text:

 

DateTimeValue(Text(MyDateVariable, "yyyy-mm-dd")&"T00:00:00.000Z"))

For example this works:

Text(DateTimeValue(Text(Date(2017,1,1), "yyyy-mm-dd")&"T00:00:00.000Z"),UTC)
1 ACCEPTED SOLUTION

Accepted Solutions
PowerApps Staff AndyPennell
PowerApps Staff

Re: Please help with UTC

We have recently identified an issue with Date (not DateTime) when serialized to/from some data sources. For right now, I recommend persisting dates as strings (something like "2017-02-08" for today) until we get the fix completed and rolled out.

9 REPLIES 9
PowerApps Staff AndyPennell
PowerApps Staff

Re: Please help with UTC

You should not need to do any conversion: just store the Date object (which internally is always in UTC) into the database. If problems persist please describe exactly what kind of database you are using, and how.

Meneghino
Level 10

Re: Please help with UTC

Hi @AndyPennell, thanks for following up.  Your replies are always precise and concise.

 

I use an Access Web App as a back-end, and then create reports in Excel with Get&Transform (a.k.a. Power Query)

 

My users enter dates (say date of birth) in a text input since a date picker is unwieldly for disparate dates.

 

Since I am in Europe, the UTC internal date turns out to be a day earlier (by a few hours), and that is what is stored in the date column by a Patch operation.  This is ok when displaying the data back in PowerApps in the same locale since it will make the conversion from UTC to local.

 

However, in Access Web Apps the date will appear as a day earlier.  Also, the Excel reporting will see it as a day earlier since it does not make the conversion from UTC to local (and a very good thing that it does not, otherwise users in different locales would see different results).  Also anyone opening the PowerApp in say the US will see the date of birth as a day earlier.

 

I have seen a few posts from European/Asian users complaining of the one day difference effect, and I suspect many US users simply have not noticed since date only local date (i.e. datetime truncated to have 00:00 time) and UTC are always on the same date.

 

I hope this is clear, and thanks for continuing to improve PowerApps.

Meneghino
Level 10

Re: Please help with UTC

PS I have just tested with CDS and the problem is the same.

 

How should one store birthdays so that they are shown as the same date irrespective of locale?

 

The only failsafe way I have thought of so far is simply to store birthdays as yyyymmdd integers, which allows for sorting and filtering for periods.  It is also obviously locale independent.

PowerApps Staff AndyPennell
PowerApps Staff

Re: Please help with UTC

We have recently identified an issue with Date (not DateTime) when serialized to/from some data sources. For right now, I recommend persisting dates as strings (something like "2017-02-08" for today) until we get the fix completed and rolled out.

sparky13
Level: Powered On

Re: Please help with UTC

@AndyPennell Has this issue been resolved?

 

It still appears to be an issue (or I may be seeing different issues)

Meneghino
Level 10

Re: Please help with UTC

Hi @sparky13, what is your specific issue?

sparky13
Level: Powered On

Re: Please help with UTC

@Meneghino. The specific issue is that comparison of dates using the date format does not work. And as a result transforming it to a text format has to be used. The limiting factor of this, is that using a date as a text format cannot be delegated.

Meneghino
Level 10

Re: Please help with UTC

MK1
Level: Powered On

Re: Please help with UTC

Hi,

In my scenario.

 

My PowerApp Form has a date time column coming form SQL Table.

while a user submitting the form, It will take the date and time of the request created by the user.

But when users from different timezones submit their request. the date column on SQL table is populated based on their timezones.

can we set the date and time column to populate as Eastern timezone for all users on Powerapp Form.

 

There is an approval process once the form is submitted.

Based on this approval process the Date and time column will be updated to the time of approval. can we set this time as one timezone for all users using flow. So that the dateand time column will be updated based on one timezone.

Please let me know your thoughts.

 

Thanks in Advance 

MK

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: 323 members 5,161 guests
Please welcome our newest community members: