Struggling to compare Dates in Filter Query

[simb55]

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.

[Brad_Groux]

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

• How to customize/format Date and Time values in a flow (microsoft.com)
• Standard date and time format strings | Microsoft Docs
• Custom date and time format strings | Microsoft Docs

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.

[simb55]

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:

with

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

with

addDays(variables('testTime'),1)

Here is the result:

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

with

dayOfMonth(utcNow())

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

[Paulie78]

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?

[simb55]

Hey Paulie78,

thank you for your response.

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

[Brad_Groux]

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: 

• Custom date and time format strings | Microsoft Docs
• Standard date and time format strings | Microsoft Docs

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.

[simb55]

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