cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sseguin
Level: Powered On

CDS Update record with new calculated Date Only

Hi,

 

I need to update records with the end date that is the start date of the new record created -1 day. Both feilds are dateOnly feild with localUser.

DateTime.jpg

 

In my flow I need to calculate the new date. No mather what kind of conversion I do I always get 2 days defore.  Ex: Start date: 2019-09-03 geting end date 2019-09-01 instead od 2019-09-02. Because the value is converted to the user timezone.

 

Here's the expression used:

SubtractFromTime(triggerOutputs()?['body/job_salarystartdate'],1,'Day')
I also tried: 
SubtractFromTime(formatDateTime(triggerOutputs()?['body/job_salarystartdate'],'u'),1,'Day')
 

Input/output

 

InputOutput.jpg

 

 

Anyone has an idea?

 

Stephen

 

 

7 REPLIES 7
Super User
Super User

Re: CDS Update record with new calculated Date Only

Hi @sseguin ,

 

Are your start and end dates always living within the same time zone? 

 

Without knowing which timezone you are in it is a bit tricky to give you the exact answer but maybe you can go for a solution based on removing X hours where X is the current time. 

sseguin
Level: Powered On

Re: CDS Update record with new calculated Date Only

Hi, 

 

The user Time zone is  (GMT-05:00) Eastern Time (US and Canada).  I tryed with a Date only feild without time Zone and the end date is populated correctly. 

 

Tks 

Stephen

Community Support Team
Community Support Team

Re: CDS Update record with new calculated Date Only

Hi @sseguin ,

 

It seems that you have found the solution,if your problem has been solved, please go ahead and mark the post as solved by clicking “Accept as Solution” so that this thread will be marked for other users to easily identify!

 

Best regards,

Alice       

 

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

sseguin
Level: Powered On

Re: CDS Update record with new calculated Date Only

Actualy it's not resolved. It's a work around for my specific situation, but I need to find a solutioon where the local timezone is required.

 

Anyone knows how to resolve this situation, or their is a problem with the connector?

Super User
Super User

Re: CDS Update record with new calculated Date Only

Hi @sseguin ,

 

I just finished a blog post about convertting time zones:

https://veenstra.me.uk/2019/09/05/time-travel-with-microsoft-flow/

 

this should complete your problem. Simply convert your time fields into the right time zone and your calculations should be ok.

sseguin
Level: Powered On

Re: CDS Update record with new calculated Date Only

Hi @Pieter_Veenstra ,

 

Thank you for your response. This does'nt resolve my problem.

I created a compose action where I Convert the date and also deduct 1 day. Here's the formula. 

subtractFromTime(convertTimeZone(triggerOutputs()?['body/job_jobstartdate'],'UTC','GMT Standard Time'),1,'Day')

 

The input and the output of the compose action is the same. But it did deducted 1 day form my start date (2019-09-05)

 

When the value is passed to the CDS Update record action, the date has changed again.

 

 

InputOutput.jpg

 

 

I also Tried converting the date to from GMT back to our time Zone 'Eastern Standard Time' It doesn't chantge any thing.

 

The more I dig into this I think theire is a problem with the CDS connector.

 

Thank you,

 

Stephan

 

Super User
Super User

Re: CDS Update record with new calculated Date Only

Hi Stephan,

 

I had another look at this. It looks like every date you  get from or send to the CDS connector has to be sent in UTC. So when you are doing an update of an item you will have to convert it back to UTC.

 

I would potentially consider to just work in UTC. So your flow runs will report the dates all in UTC. ?You can then do your calculations ( e.g -2 days ) and update the items in the utc time zone.

Helpful resources

Announcements
firstImage

New Ranks and Rank Icons in April

Read the announcement for more information!

firstImage

Better Together Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Community Summit North America

The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (8,581)