MS Flow: SQL Server Get_rows - We cannot apply operator < to types DateTimeZone and DateTime

Question

Problem

I am trying to write a simple Flow that gathers some rows from my SQL Server database and sends the data to me via email. However, I am having trouble filtering these records by date. I want the Flow to only retrieve the records from the past day so I am trying to do something such as the following Odata filter query:

ItemCreatedWhen gt addDays(utcNow('yyyy-MM-ddTHH:mm:ssZ'), -1)

When I run this however, I get the following error message:

We cannot apply operator < to types DateTimeZone and DateTime.

The SQL Server datatype of this column is datetime2 in the form of 'yyyy-MM-dd HH:mm:ss'

What I've Tried

I have tried the following:

ItemCreatedWhen gt addDays(utcNow(), -1)

Error: We cannot apply operator < to types DateTimeZone and DateTime.

ItemCreatedWhen gt convertFromUtc(addDays(utcNow(), -1), 'Eastern Standard Time')

Error: The DateTimeOffset text '2019-01-24T10:59:25.7848207' should be in format 'yyyy-mm-ddThh:mm:ss('.'s+)?(zzzzzz)?' and each field value is within valid range. inner exception: The time zone information is missing on the DateTimeOffset value '2019-01-24T10:59:25.7848207'. A DateTimeOffset value must contain the time zone information.

score 4 · Answer 1 · answered Nov 10 '21 at 09:25

We have recently hit this issue with a PostgreSQL connector (it is generally an issue with database connectors in general in Power Automate/Logic Apps).

My colleague wrote a blog post about this and what has to be done if you want to filter by DateTime. Generally, the problem is, that the Power Query implementation (the thing which executes the query against the database) doesn't properly work with DateTime/DateTimeZone and similar fields (this is further described in SQL Server Connector's limitations, and it also applies to other database connectors as well (like PostgreSQL).

The solution is to use year, month, day, hour, minute functions which convert the DateTime value to numbers which you can then use to compare. This results in a quite challenging query which has to be executed, because you can't simply call:

mytime = "2020-12-31T10:00:00Z`
year(dbfield) ge year(mytime) and month(dbfield) ge month(mytime) and day(dbfield) ge day(mytime)

This would omit a lot of values due to direct month comparison which would rule out month 1 to 11 and same for day, ruling out 1 to 30.

In order to prevent this, you have to make a much more complex query (I formatted it, so it is more readable):

(
    year(change) gt year(@{variables('Timestamp')})
    or
    (
        year(change) eq year(@{variables('Timestamp')})
        and
        (
            month(change) gt month(@{variables('Timestamp')})
            or
            (
                month(change) eq month(@{variables('Timestamp')})
                and
                (
                    day(change) gt day(@{variables('Timestamp')})
                    or
                    (
                        day(change) eq day(@{variables('Timestamp')})
                        and
                        (
                            hour(change) gt hour(@{variables('Timestamp')})
                            or
                            (
                                hour(change) eq hour(@{variables('Timestamp')})
                                and
                                (
                                    minute(change) ge minute(@{variables('Timestamp')})
                                )
                            )
                        )
                    )
                )
            )
        )
    )
)

I have a ticket open about this with Microsoft, because I really would love to just run a regular query for date comparison, because it would make my life much more easier (reference #2111080010002309).

score 0 · Accepted Answer · answered Jan 25 '19 at 16:53

0

Assuming column "ItemCreatedWhen" in your table is in date/time format, just send that query to SQL Server to get yesterday's data:

SELECT * FROM YourTable
WHERE ItemCreatedWhen > DATEADD(DAY,-2,CAST(GETUTCDATE() as DATE)) 
    and ItemCreatedWhen < CAST(GETUTCDATE() as DATE)

answered Jan 25 '19 at 16:53

Slava Murygin

1,951
1
10
10

1

I was inspired by this and ended up creating a view on SQL Server and queried that for the rows I needed. Here is the query: `SELECT SignupsEmail, ItemCreatedWhen FROM NewsletterSignups WHERE (ItemCreatedWhen > DATEADD(day, -1, GETDATE())` – Tanksley Jan 28 '19 at 14:45
I have the same situation, except I had already thought of creating the view on the sql server, but its a 3rd party product and I don't have access to create SQL views. CREATE VIEW permission denied in database 'IDX_db'. – hamish May 24 '20 at 09:26

MS Flow: SQL Server Get_rows - We cannot apply operator < to types DateTimeZone and DateTime

Problem

What I've Tried

2 Answers2