cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BSchaap
New Member

DataVerse date value incorrect (time-zone issue?)

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 😊.

BSchaap_1-1618990476827.png

 

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.

BSchaap_2-1618990476831.png

 

Figure 2

Here is the weird part:

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

BSchaap_3-1618990476834.png

 

Figure 3

This is the output of this flow:

BSchaap_4-1618990476837.png

 

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

1 ACCEPTED SOLUTION

Accepted Solutions

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.

View solution in original post

5 REPLIES 5
Rilsina
Resolver II
Resolver II

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.

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

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
New Member

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 LocalDate 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 OnlyDate OnlyNo 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 IndependentDate 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
Advocate II
Advocate II

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')

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

Carousel_PP_768x460_Wave2 (1).png

2022 Release Wave 2 Plan

Power Platform release plan for the 2022 release wave 2 describes all new features releasing from October 2022 through March 2023.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Users online (4,697)