Showing results for 
Search instead for 
Did you mean: 
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:



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



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

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 (

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:









Here is the result:





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






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.

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

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


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



This leads to the same error I got before.

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


Helpful resources

Register for a Free Workshop.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Users online (2,249)