cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jnovoa36
Frequent Visitor

SharePoint Get Items, Filter Query by Date

Hi, 

 

I am currently working on a flow that will be able to pull information from a SharePoint List by date. It is a bit complicated because I want the system to be able to tell what day it is and then get the information from this today/this week and also all the information from the previous two weeks (fully Mon-Sun). Is there a way to do this using the ODATA Filter Query in Get Items from SharePoint?

 

I am currently using: 

Created le '@{utcNow()}' and Created gt '@{addDays(utcNow(),-7)}'

 

For reference, the column I need is titled Created and this is running fine, but I am in Pacific standard time not UTC (which is what it is automatically set to), so I need to change that as well and I am in need of the above date information. 

 

I would love some support, thank you!

1 ACCEPTED SOLUTION

Accepted Solutions

@jnovoa36 

Your initial filter query looks good to me..so simply after conversion your query should be something like created gt it now()-14 and created Lt today and I suppose you will be running this on a schedule that runs weekly on Monday for example or daily I am not sure of your logic , also to save as date only you need to use formatdatetime expression while saving to excel.

 

 

 

If this post helps you with your problem, please mark your as Accepted solution.If you like my response, please give it a Thumbs Up.

Blog: here

View solution in original post

6 REPLIES 6
Mira_Ghaly
Dual Super User
Dual Super User

@jnovoa36 

You can use the below action to convert from UTC to your timezone and then use the output of this action in your filter, of course you need 2 actions one for the UTC Now and one for the UTCNOW -7 days, Please notice the format as well in the Convert Time Zone Action

Mira_Ghaly_0-1657232316026.png

Hope this helps!

 

If this post helps you with your problem, please mark your as Accepted solution.If you like my response, please give it a Thumbs Up.

Blog: here

Thank you! I wonder, I have a Convert Time Zone action when I am adding the information from the SharePoint List to an Excel file. Is there a reason why the UTCNOW won't fix itself then? Would I need a secondary Convert Time Zone Action?

@jnovoa36 

I am not sure what you mean , do you mean you are saving as UTC in SharePoint?

 

If this post helps you with your problem, please mark your as Accepted solution.If you like my response, please give it a Thumbs Up.

Blog: here

The Created column in SharePoint I believe is set to local time. Something we are noticing is that using utcNow() is setting our output at UTC (giving us date and time) rather than PST (date and time). I made the changes to my flow per your recommendations to keep everything in PST but when I go use the Apply to Each action in order to pull the information from SharePoint to an Excel file I also have a Convert Time Zone function. 

 

I removed it to test and now my output is date time rather than just date. I am wondering if I should keep my Convert Time Zone action in the Apply to Each action or not. 

 

I'm also not sure what to do with my first question, which is to use the Filter Query in the Get Items action so that I am able to get the information I need from SharePoint. I need to be able to see Today/This Week's information and the previous two weeks (Full Mon-Sun). 

@jnovoa36 

Your initial filter query looks good to me..so simply after conversion your query should be something like created gt it now()-14 and created Lt today and I suppose you will be running this on a schedule that runs weekly on Monday for example or daily I am not sure of your logic , also to save as date only you need to use formatdatetime expression while saving to excel.

 

 

 

If this post helps you with your problem, please mark your as Accepted solution.If you like my response, please give it a Thumbs Up.

Blog: here

Thank you! Yes you are right this is the simplest form to do it. I may need to go back and rethink this in terms of my flow as it will get more complicated as I continue but this worked for me!

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Users online (3,031)