cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
simb55
Advocate II
Advocate II

Struggling to compare Dates in Filter Query

Hi all,

 

I have a flow, which collects and pocesses data from a table in Azure SQL DB.

Up until now, I fetched all the data via 'Get rows' and ran over it with 'Filter array' afterwards, which worked just fine.

Recently we recognized, the processed data was incomplete.

 

I found quickly, that the JSON from 'Get rows' only contained 2.048 items and realized that a freshhold has been hit. Now we could simply raise the threshhold, but as the records in the table keep growing, we would encounter the same problem again, soon.

 

So I tried to include an oData Filter Query in my 'Get rows' action that would make the 'Filter array' action obsolete. What I want is to get all rows, where the timestamp column ('datum') in my table is within the current month. So I tried the following expression:

simb55_1-1604415536004.png

 

with the expression

 

formatDateTime(utcNow(), 'yyyy-MM-01')

 

 

I tried several variants of the above but I kept getting the following error with code 400

simb55_2-1604416090945.png

 

"We cannot apply operator < to types DateTimeZone and DateTime."

 

While I can confirm, that the expression gave me a proper DateTime value (I tried calculating with the expression, it worked; I tried the 'Get past time' action to make sure it is a proper DateTime value), I suspect that the problem is the value retrieved from the DB. More accurately I think datum is in DateTimeZone format.

 

I could not find a way to convert the DateTime value to DateTimeZone or the other way around. Also I do not know, how to modify the value retrieved from 'datum' inside my oData Filter Query statement.

 

I would appreciate any help with this! Thanks in advance.

6 REPLIES 6
Brad_Groux
Community Champion
Community Champion

Your date/time function isn't using a supported format. See here:

Your issue is with the 01:

formatDateTime(utcNow(), 'yyyy-MM-01')

It should look more like: 

formatDateTime(utcNow(), 'yyyy-MM-dd')

More examples here: formatDateTime to Format Dates with Power Automate (sharepains.com)

If this reply answers your question or solves your issue, please ACCEPT AS SOLUTION ☑️. If you find this reply helpful, please consider giving it a LIKE.

Hi Brad_Groux,

 

thank you for your response.

 

Afaik this is a legitimate way to set up a DateTime value. I have made a little test dflow to prove my point:

simb55_0-1604425730344.png

with

formatDateTime(utcNow(),'yyyy-MM-01')

simb55_1-1604425790152.png

with

addDays(variables('testTime'),1)

 

 

Here is the result:

simb55_2-1604425856654.png

 

 

 

However, I have also tried using the 'Get past time' action like this to set up my DateTime value

simb55_4-1604426510054.png

 

with

dayOfMonth(utcNow())

 

which leads to the same error stated in my original post.

What is the column type in SQL? You mention TimeStamp above, timestamp has really nothing to do with Date or Time. Is it a datetime column?

Hey Paulie78,

 

thank you for your response.

I just looked it up to confirm that it is indeed a datetime column.

Brad_Groux
Community Champion
Community Champion

Just because the DateTime is returning a value doesn't mean that it is a supported specifier. I linked the documentation above, and the specifier you are using, is not listed: 

Further, the official How to customize/format Date and Time values in a flow documentation points to those two specific best practice documents.

So, per the documentation, the specifier you are using is not officially supported. As with all things in the Microsoft ecosystem, working outside of best practice, your mileage may vary and doing so isn't officially supported. 

If this reply answers your question or solves your issue, please ACCEPT AS SOLUTION ☑️. If you find this reply helpful, please consider giving it a LIKE. Want to know more? Follow @BradGroux on Twitter, and check out my Medium blog at https://msft.engineer.

Hello Brad_Groux,

 

while what you say is true, it does unfortunately not solve my problem.

I have edited my flow to use 'Get Past Time' action to get the right DateTime value to compare to

simb55_0-1604912512712.png

and formatDateTime(...,'yyyy-MM-ddTHH:mm:ss.fffZ') to format the value to match the format in my database

simb55_1-1604912841025.png

 

This leads to the same error I got before.

"We cannot apply operator < to types DateTimeZone and DateTime."

 

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.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors
Users online (1,177)