SharePoint Get Items, Filter Query by Date

[jnovoa36]

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!

[Mira_Ghaly]

@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

Hope this helps!

[jnovoa36]

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?

[Mira_Ghaly]

@jnovoa36 

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

[jnovoa36]

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

[Mira_Ghaly · Accepted solution]

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

[jnovoa36]

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!