cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Microsoft
Microsoft

date related filter query to SQL database

I could not get the expected results when using Get Rows and Filter Query on date related fields. However Filter Query works with String or ID fields.

 

I stored two dates (DateFrom and DateTo) in the database, and to troubleshoot, I later changed to datetime. Database is in Azure SQL, connected using on premise gateway.  The data itself does not have time and only date. 

 

I tried all the following filter query for DateFrom and DateTo, and all cases except the last find no records. 

1) Construct a new string variable that contains year, month or day function separately. This approach of creating a string can spin the head very quickly using dynamic variables from earlier flow steps.  

day(DateFrom) eq day(2019-12-02T00:00:00Z) and month(DateFrom) eq month(2019-12-02T00:00:00Z) and year(DateFrom) eq year(2019-12-02T00:00:00Z) and day(DateTo)   eq day(2019-12-06T00:00:00Z) and month(DateTo)   eq month(2019-12-06T00:00:00Z) and year(DateTo) eq year(2019-12-06T00:00:00Z)

 

In addition, day, month and year are not functions available in Flow, so can you enable them if using those functions on the dynamic variables is required to construct the Filter Query instead of using variables as date directly as below alternatives.    

 

2) Without quote for actual dates 
DateFrom eq 2019-12-02T00:00:00Z and DateTo eq 2019-12-06T00:00:00Z
3) With quote
DateFrom eq '2019-12-02T00:00:00Z' and DateTo eq '2019-12-06T00:00:00Z'
4) Without time, quoted
DateFrom eq '2019-12-02' and DateTo eq '2019-12-06'
5) Without time, slashed, quoted
DateFrom eq '2019/12/02' and DateTo eq '2019/12/06'
6) Without time, slashed, without leading zero for day, quoted
DateFrom eq '2019/12/2' and DateTo eq '2019/12/6'
7) With datetime and does not pass 
DateFrom eq datetime'2019-12-02T00:00:00Z' and DateTo eq datetime'2019-12-06T00:00:00Z'

😎 With integer as below but error message said The $filter expression must evaluate to a single boolean value. I do have many rows with the same DateFrom and hope to get them back as a group of rows. 

day([DateFrom]) eq 2 and month([DateFrom]) eq 12 and year([DateFrom]) eq 2019

Hope it is easier, and I found other posts in the community about the difficulty with date or datetime fields. Thanks. 

Helpful resources

Announcements
firstImage

New Ranks and Rank Icons released on April 21!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power Automate Community!

firstImage

Now Live: Power Virtual Agents Community!

We are excited to announce the launch of Power Virtual Agents Community. Check it out now!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Community Summit North America

The top training and networking event across the globe for Microsoft Business Applications

Top Kudoed Authors
Users online (5,937)