cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Super User II
Super User II

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.

Super User II
Super User II

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
New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

MPA Community Blog

Power Automate Community Blog

Check out the community blog page where you can find valuable learning material from community and product team members!

Users online (107,981)