Date in PowerApps

[Anonymous]

Time is quite a headache in PowerApps if you don't live in the States.... 

The latest problem I have is the date registered in CDS is not the same as the date show inside the app, it's one day behind... 

First, inside the app I have a datepicker which pick ups date which shows today by default. It wasn't working in the beginning, as when I click the datepicker it uses US dates automatically, so the date is completely confused, but now it is working fine. Don't know why. 

Then I use the date picked to filter a list of potential tests the users can perform: 

ClearCollect(POlist,Sort(Filter(PO,And(DateValue(Text(SlaughterDate),"nb-NO")=DateValue(Text(SlakteDatePicker.SelectedDate),"nb-NO"),Slakteri=Gallery_Slakteri.Selected.Result)),Tilgangsnr,Ascending))

Here I already needs to specify the format of the dates comparison, and the datevalue is actually completely wrong (for example it shows some time next year), but at least the comparison gave me the correct result. 

Then I use a global variable to register the date picked:

Set(datoselected,DateValue(Text(SlakteDatePicker.SelectedDate,"[$-en-US]dd/mm/yyyy"),"nb-NO"))

This is show in correct date and format inside the apps. 

But when I write the dates into CDS, the date is wrong again... Same issue if I use the correct dates in the comparison formula above, the dates write into CDS will be a future date... 

Total crisis! Can someone help me with it?

Best,

Juliet

[Pstork1]

I agree that working with Dates is difficult.  The primary reason stems from two things. 

1) DateTime values always include a time component even when all you want is a date. So a DateTime value provided by a DatePicker is actually the Date with the time set to 00:00:00 or midnight. That's why using equals in a comparison doesn't work since the time won't match even if the date does.

2) DateTime values stored in CDS (or SharePoint) are always stored as Universal Time Code (UTC) or GMT.  But they are displayed automatically in the local time of the user. So a date stored with Midnight may actually be displayed as a date with 1:00 AM in the Netherlands (one day later).

In general the way you get around most of this is to translate the datetime into a string of Date only while its still UTC.  Then use that to compare to a Date string.  That takes time out of it and actually compares the date entered to the date the user thinks they picked.

Another issue wrapped up in this is that the system will automatically pick up the locale settings for the user in the Browser.  This can shift the way dates are formatted.  The default is EN-us.  In some of the formulas you list you are specifying 'nb'.  If you do that you need to be consistent everywhere.  I also see an EN-us in your code.  Mixing locales, either by hard coding them or by letting it default to the user can cause strange results.

I agree that working with dates is difficult.  If you understand what is going on behind the scenes it doesn't necessarily make it easy, but it does make it possible to adapt your code to get it to do what you want.

[Anonymous]

Thanks for the tips! It's definitely very useful to know what is happening behind the scene. But I still didn't manage to solve the problem. It seems like even if I tried to write Today() into CDS, it's still not correct. If I try to get the string value of Today() then convert it back to DateValue in "nb-NO" format, it shows some dates in 2021. I also checked the advanced option for the field I set up in CDS, its behavior is set to user local. So really not sure why it is still a day different when I write correct result into CDS. 

[Pstork1]

The one thing I would say is converting the date to 'NB-no' format will change the date, but I don't think it will adjust the timezone.  That has to be done manually.  As I said, its something that everyone struggles with.  My other suggestion would be to always put a label somewhere while troubleshooting where you output the full date and time string.  That can help explain the date shifts sometimes. The one time I've seen the actual year change I was using a custom format string and I had an error in the string.

[Anonymous]

How can I manually change my timezone setting then?  It seems like my computer is set to the "en-us" locale, according to PowerApps documentation. But I checked my windows, my browser, and my azure account, I cannot seem to find anywhere that has a setting to "en-us" locale... It seems like this is really causing problems. Even though I used lots of labels to show the process of my time conversion, but if it's right in the variable, it's wrong in the collection and CDS, if it's right in the collection, it's wrong in CDS, variable and in HTML text where I try to show the report... The best scenario was it's only wrong in CDS, but that somehow disappeared today... 

[Pstork1]

I suspect its picking up the timezone settings for your Dynamics installation.  Check with your Office 365 admins to see what your tenant's default timezone is. I suspect that is where you are getting the timezone setting.

[Luke_Timmins]

Sometimes I convert the field in CDS to a text field and store the date as a string. Then you dont have the whole date conversion issue. What you see on your screen is what is saved to CDS. 

This is particularly an issue when you use a SQL Server data source and try to compare dates. 

Thanks, 

Luke

[Pstork1]

Converting it to a Date Only string and using that is definately one way to get around the issue.  I normally recommend people do that whenever they are trying to evaluate one date versus another.

[NHelgren]

I agree with Pstork1's suggestions. In addition, the following document provides information about storing time zone independant values vs UTC values that will be interpreted against the user's local time: 

https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/behavior-format-date-time-field...