Dataverse Date only field is set 1 day off from original value

[Billy_C]

Hi there,

So I am having issues with dates being retrieved by my Flow from the cds.

Let's say I have a model driven app with 2 tables (entities) project and invoice which both have the column (field) paid on which is a date only field so timezones shouldn't matter as its date is fixed.

I edited one of my invoices paid on date only field to 9/2/2021 and I executed a flow that creates a payment for that invoice and updates the project with that date.

When I take a look at the invoice I see that all of a sudden the paid on value in the project is 8/2/2021.

As you can see in the below screenshots:

Invoice date: 

https://i.imgur.com/fZBdCan.png 

Project date (bottom right):

https://i.imgur.com/BO44EIt.png 

and when taking a look at the flow itself you can see that the retrieved date from the invoice itself is incorrect:

https://i.imgur.com/RBpy6bl.png 

Any ideas why this issue is occuring?

Thanks in advance!

[PrasadAthalye]

Hi @Billy_C 

In the Column Properties, what is the Behavior value of the column you have an issue with? Is it User Local OR date Only?

[Billy_C]

Hi @PrasadAthalye,

Yes the behavior is user local, although this isn't the issue.
Let me explain in detail:
CDS date 8/2/2021
flow date 7/2/2021
set cds date from flow 7/2/2021
view cds date from powerapps 7/2/2021 <-- this is not correct this should be back to its user local timezone.

If I would do this process again will the date decrease every time, this is not what should happen normally.

What one would expect to happen (even with user local fields) is flow gets the date its okay if it is a different date or not, but if it passes the value back to powerapps this value should be save in the same timezone that power automate retrieved it, so If I would view the value from powerapps it shows the date in my timezone which would be the original value.

So basically i start in timezone a flow in b and passes it back to powerapps in timezone b then powerapps will show it in timezone a (in my case) instead it overwrites the timezone b as being timezone a and making it show the wrong date.

So somewhere between power automate and powerapps there is a timezone conversion issue I think.

Ofcourse in my case its a fixed date so i can change the behavior but in cases where it is supposed to be timezone dependent dates only, this definitely shouldn't happen.

Anyways I will change it's behavior like you said, but I will not put this as solved because the issue will still remain for timezone dependent cases.

Thank you for your answer!

Kind regards,

Billy Cottrell

[PrasadAthalye]

@Billy_C  Thanks.

Are you located within the same Timezone where your tenant is located? If not, what is your timezone and tenant's time zone?

[Billy_C]

Hi @PrasadAthalye,

So my time zone is UTC+1, now I don't where to look for this setting but I have been looking around where this setting could be.

I have found a setting in the powerapp itself (settings -> personalization settings):

https://i.imgur.com/39HupIZ.png 

I wanted to see in the powerapps admin center if I could see in the settings what timezone is being used, but for some reason I can't enter the settings anymore (this for all my environments the case) and I am getting the following error:

https://i.imgur.com/eKZIOIA.png

https://i.imgur.com/Jm79Ng8.png 

or sometimes this error:

https://i.imgur.com/avx7llX.png 

So the last place I looked for this was in the Powerapps startpage -> settings -> change language and timezone and then I found another timezone setting which is also UTC+1:

https://i.imgur.com/lw8pfZg.png 

If there are any other locations I need to look at please let me know.

Thanks in advance!

Kind regards,

Billy Cottrell

[PrasadAthalye]

Please refer to the following post. 

https://powerusers.microsoft.com/t5/Building-Power-Apps/Setting-PowerApps-time-zone/td-p/258263 

Currently, when working date-time values within PowerApps or Microsoft Flow, the date-time values would be converted into UTC format automatically.

In addition, the Time Zone of your PowerApps is based on your local time zone in default.

[Billy_C]

Hi,

So since my local timezone is UTC+1 and it gets converted to UTC it goes from 9/2/2021 to 8/2/2021 and I think that's perfectly fine but when it is saved in a field the timezone difference isn't calculated back to the original value, meaning that if the day is converted to UTC and it gets saved in powerapps it just retains the date but it doesn't take the timezone into account so its saved as a UTC+1 date instead of a UTC date. This way powerapps isn't able to convert this date into the original value 9/2/2021 with a timezone of UTC+1.

I think that flow has to provide a way to know the timezone difference so that if we are saving dates into a record we are actually converting the date into its original value so the user will always have the correct dates, or atleast provide this information inside the json output. The other method would be that if flow is saving these dates to powerapps that the flow step itself should know the timezone difference so it is saving it in the correct timezone.

Because let's say flow does an update and its timezone is no longer UTC due to some reasons. Then we have no method to make sure that our dates are being saved in the correct timezone.

Nonetheless there should be a way to make sure that the dates that are saved from flow to powerapps can be viewed by the user in the correct time zone with the correct value.

Thanks in advance!

Kind regards,

Billy Cottrell

[PrasadAthalye]

Please refer to the below link that explains the behavior of the Date Time column & some examples... Since you have now changed it to DateOnly, your new records should display the date correctly.

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

[Billy_C]

Hi,

Yes the fields I have right now have been changed to a behavior of Date only instead of User Local. What I was saying was in case I would need date only fields to have a behavior of User Local. Since these cases will still have this issue.

Kind regards,

Billy Cottrell

[khoait]

Hi,

My case is UTC+0 Dublin, Edinburge, Lisbon, London, and the dateonly column in my flow returns the previous day. If it is a date and time column, the time part should be T23:00:00, but because it is a dateonly, it only shows the date part with one day off.

My solution is to add 23h to the value in the flow and convert it from UTC to UTC+1.

Hope it helps!

Khoa

[Billy_C]

Hi @khoait,

It is true that this will solve the issue, but that's if you remain in that timezone. I am afraid that if you are moving to a different timezone (or its a user from a different timezone) and do the same thing it won't work as the result might be different like the date might be right, or it might even be the next day instead of the previous day depending on your timezone and current time.

Either way you shouldn't have to convert the timezones of dates it should be something that has to be converted by flow or powerapps.

Thanks in advance,

Billy Cottrell