Answered

Flow Query Filter Date Range

(2) Share

Report

Posted on by Bahalzamon

Basically I am doing a Get Items and attempting to filter the data based on a date range. I have come across MANY different suggestions and have attempted them all with no luck.

Here is what I am working with:

From Date: Date Only

To Date: Date Only

The Get Items works properly with no query and returns everything, but I want to filter based on passed in dates.

Here is how it currently looks:

10-29-2020 10-25-03 AM.jpg

I have tried many different options for converting the date values such as datetime'fromDate' with no luck.

All I receive is the filter is not valid.

Thanks for any help. 😃

Categories:

Building flows

All responses (30)

Answers (1)

Sort by

Verified answer

Bahalzamon on at

Like
a
(7)

Report
Copy link

Link copied!
After about a week of messing with it I make a post and figure it out 15 minutes later lol

Here is what it ended up being:
From Date:
formatDateTime(triggerBody()?['From'],'yyyy-MM-dd')
To Date:
formatDateTime(triggerBody()?['To'],'yyyy-MM-dd')
WC-KSTILL 117 on at

Like
a
(0)

Report
Copy link

Link copied!
How did you write in the dates?

Like this?

formatDateTime(triggerBody()?['From'],'2019-01-01')
?
Bahalzamon on at

Like
a
(0)

Report
Copy link

Link copied!

@WC-KSTILL
Kind of, the portion where you have the '2019-01-01' needs to be how I typed it as 'yyyy-MM-dd' as formatDateTime takes 2 arguments, the first is a date/time value, for my case I had a column named From, so triggerBody()?['From'] is my date.
The second is what format you want to date to be returned as. Here is the documentation on it, you can use any or all of these letters formatDateTime('<your-value>', 'dd/MM/yyyy hh:mm tt') which denote the different type of date/time information you want returned.
WC-KSTILL 117 on at

Like
a
(0)

Report
Copy link

Link copied!

I still can't figure this out...

I am trying to get all items created on or after 01-01-2018

I tried a combination of things.

I had this working but it suddenly stopped, no idea why. It returns 0 results now

Created ge '@{getPastTime(1033,'Day')}'

How can I use your formatDateTime saying Created Date anything older that 01-01-2018
WC-KSTILL 117 on at

Like
a
(0)

Report
Copy link

Link copied!

I meant, anything equal to or newer than 01-01-2018
WC-KSTILL 117 on at

Like
a
(0)

Report
Copy link

Link copied!

I also tried

formatDateTime('2018-01-01T00:00:00', 'M/dd/yyyy h:mm tt')
Bahalzamon on at

Like
a
(0)

Report
Copy link

Link copied!

@WC-KSTILL
If you have a static date you can just do Created ge '2018-01-01' you are only passing in a string in the format of 'yyyy-MM-dd' (yyyy=year, MM=month, dd=day)
You can use a variable or compose a data value, or basically anything to get the info in there. ensure you have the ' on both ends of what you are passing in though.
WC-KSTILL 117 on at

Like
a
(0)

Report
Copy link

Link copied!

I tried your suggestion, still 0 results. I am going to re-create this from scratch.. I feel like its bugged or something
Bahalzamon on at

Like
a
(0)

Report
Copy link

Link copied!

@WC-KSTILL Are you inserting an expression or just manually typing this into the area?
WC-KSTILL 117 on at

Like
a
(0)

Report
Copy link

Link copied!

I figured it out...I ahd to go to settings and turn on the threshold. Never had to do that before

Created gt '2019-01-01T00:00:00Z'