cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mayarnaldo
Helper II
Helper II

Filtery Query year from date

Hi,

 

I am working on Get Items from SharePoint list and I am using Filter Query to get year from the date.

 

When I am using the Column name: End_of_Period, I got and error of Column doesn't exist.

 

When I go to the List Setting and get the name at the link: End_x0020_of_x0020_period, I got error of String was not recognized as valid DateTime.

 

Anyone can help me with this?

 

Thank you! 

 

Regards,

May

 

 

 

01.jpg02.jpg03.jpg

1 ACCEPTED SOLUTION

Accepted Solutions
yashag2255
Dual Super User II
Dual Super User II

Hi @mayarnaldo 

 

Note that to use the ODATA 'eq' condition filter for date type columns, you need the date string to be appropriately formatted. 

 

So that query needs to be modified as: End_x0020_of_x0020_period eq '@{formatDateTime(utcNow(),'yyyy-MM-dd')}'

 

But this will get you items only for the current date. A few workarounds you can try out: 

1. Create a calculated column in SP and set the calculation to get the year from the date type column and then use that to filter in the Flow. 

2. List all records in the Get Items action and then add a condition to check if the Date starts with @{formatDateTime(utcNow(),'yyyy')}

 

Hope this Helps!

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

View solution in original post

3 REPLIES 3
yashag2255
Dual Super User II
Dual Super User II

Hi @mayarnaldo 

 

Note that to use the ODATA 'eq' condition filter for date type columns, you need the date string to be appropriately formatted. 

 

So that query needs to be modified as: End_x0020_of_x0020_period eq '@{formatDateTime(utcNow(),'yyyy-MM-dd')}'

 

But this will get you items only for the current date. A few workarounds you can try out: 

1. Create a calculated column in SP and set the calculation to get the year from the date type column and then use that to filter in the Flow. 

2. List all records in the Get Items action and then add a condition to check if the Date starts with @{formatDateTime(utcNow(),'yyyy')}

 

Hope this Helps!

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

View solution in original post

Hi @yashag2255 ,

 

Thank you for your reply! 🙂

 

I will try the first solution.

 

I tried the workaround that you mentioned. I created anew column and get the year from the date. I used that year column but I got an error, saying it cannot be used because it is a calculated field.

 

 

Regards,

May

 

 

Hey @mayarnaldo 

 

Yeah my bad. Calculated columns are not filter friendly. You can try out the second option for this to compare in the condition. 

 

Hope this Helps!

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (2,499)