Truncating date/time column values to just date

(0) Share

Report

Posted on by steveohan

Hi everyone,

I have a SharePoint list that I'm using to track training sessions delivered by our team.

I'm looking to create an automation that will run on a nightly basis to change status of sessions from 'Posted' to 'Completed' based on the session date and the current date. Each session has a start time and and end time stored as date/time (along with a variety of other fields).

I'm having a heck of a time however pulling the End Time of a session and truncating to remove the time. How do I go about achieving this?

I was thinking of initially using a get items action with a filter query of End Time eq Now (truncating to the day) to just compare dates. In SQL I would just use the trunc function for something like this.

Can anyone lead me in the right direction?

Thank you!

Categories:

Building flows

I have the same question (0)

All responses (3)

Answers (0)

wskinnermctc 6,519 Moderator on at

Like (1)

Report

If the column has full DateTime 8-29-2023 14:55 and then you try to filter as equal to 8-29-2023 it will never match.

You will have to do something to make a range and get time in between using greater than gt and less than lt

If you wanted to get End Times for todays date.

(End_Time gt startofday(utcNow()) ) AND (End_Time lt addDays(startofday(utcNow()),1) )

So that will get any DateTime between 8/29/2023 00:00 AM and 8/30/2023 00:00 AM

You can work with the date times how you want, but do you get the idea?

https://learn.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#startofday

https://learn.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#adddays

https://learn.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#utcnow

Was this reply helpful? Yes No
steveohan 11 on at

Like (0)

Report

Thanks for your response!
Yup I get the idea of defining a range, but I was hoping to truncate to avoid/simplify that if its possible.

So basically End Time (truncated to the day to remove the time) eq utcNow (also truncated to the day to remove time).

Was this reply helpful? Yes No
wskinnermctc 6,519 Moderator on at

Like (1)

Report

You can't edit/adjust any value that is in the SharePoint List in the Get Items field Filter Query.

Something like formatDateTime({End_Time},'mm-dd-yyyy') will not work inside the Get Items.

It doesn't work because it is basically trying to format a value in an expression that it doesn't have yet.

You would have to Get Items and then add a following step Filter Array to apply to all of the Get Items outputs with the filter array.

Was this reply helpful? Yes No