DataVerse date value incorrect (time-zone issue?)

[BSchaap]

Hi all,

I have this weird problem, which is really annoying me, it is about date’s:

To test it I created a table in DataVerse (CDS) called ‘Bull**bleep**’, like I said I am annoyed 😊.

Figure 1

It has two columns one with CDS datatype DateOnly and one with CDS datatype DateTime.

As you can see in figure 1, I entered a date in European style 1-4-2021 which means April first in both fields (not a joke!). This was done using an auto generated power app see figure 2.

Figure 2

Here is the weird part:

I made a small flow which reads these two fields, see figure 3:

Figure 3

This is the output of this flow:

Figure 4

As you can see it displays this date as March 31 instead of April 1!

My suspicion is that this is somehow connected to a time zone. I am located in Amsterdam which is currently UTC-2. As the date is entered as 4/1/2021 12:00 AM it all depends on what this means and how it is interpreted.

In Europe we use 24 hour notation, so there is no confusion but what does 12:00 AM mean? Given the above behavior I think it means 00:00 in 24 hour notation. When 2 hours are subtracted then then date will become 1 day earlier, hence March 31.

My question, how can I get Flow to read the correct date?

FYI: these date’s are used in Dynamics 365 and strangely enough in D365 the dates remain correct, only when retrieving them and using them in Flow (in my case used in an email for notification) I get this behavior.

Best regards,

Bart Schaap

[Rilsina]

Hi,

Hopefully this blog will help-https://www.cloudfronts.com/solved-date-field-shows-wrong-date-in-power-automate/

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BSchaap]

Hi,

The blog is incorrect in my opinion. I tried both field types (datetime and dateonly). They behave the same as you can see in my post.
As for the workaround: this is not what I am looking for. Simply adding a day is not a reliable solution to this problem.

Best regards

Bart Schaap

[BSchaap · Accepted solution]

Found the solution myself 😀

Use convertTimeZone function using UTC as source timezone and in my case 

W. Europe Standard Time as destination timezone. Now I get the correct date in flow.

[jburston]

I realise this is an old post but I have found an alternative solution which may help others who hit this issue and find this post. I think it's always better practice to try and fix underlying data issues (if practicable), directly within the 'golden-source record' rather than applying 'fixes' further down-stream. Hope this helps someone else as it did me - I changed my fields behaviour from Local to DateOnly and that fixed all of the timezone issues I was experiencing across flow and power bi.

Date and time column behaviour and format

The following table contains information about the date and time column behaviour and format.

DATE AND TIME COLUMN BEHAVIOR AND FORMAT behaviour Format Description

User Local	Date Only - or - Date and Time	This is the default behaviour of custom date and time columns. The column values are displayed in the current user's local time. In Web services, these values are returned using a common UTC time zone format. You can change this one time if you select the default behaviour. More information Change User Local Behaviour
Date Only	Date Only	No Time zone conversion. The time portion of the value is always 12:00AM. The date portion of the value is stored and retrieved as specified in the UI and Web services.
Time-Zone Independent	Date Only - or - Date and Time	No Time zone conversion. The date and time values are stored and retrieved as specified in the UI and Web services.

full link here:

Behavior and format of the Date and Time column in Microsoft Dataverse - Power Apps | Microsoft Docs

[maxpower45255]

Here's an example of how you can use the convertTimeZone() function, wrapped in a formatDateTime(). 

formatDateTime(convertTimeZone(utcNow(), 'UTC', 'Eastern Standard Time'), 'yyyy-MM-dd HH:mm:ss')