SQL connector Get Rows action filter query for datetime error

(0) Share

Report

Posted on by vissvess

Dear Community Members,

I want to filter out rows from SQL using Get rows action. The SQL is in On-prem with Gateway functioning properly.

I ensured proper connection with the SQL for flow with Top count as '1' for testing. This resulted in a valid response.

However, when I use filter query ( OData) like,

MODIFIEDDATETIME ge '2021-01-23T05:07:59.5285205Z'
MODIFIEDDATETIME ge datetime'2021-01-23T05:07:59.5285205Z'
year(MODIFIEDDATETIME) ge 2021 and month(MODIFIEDDATETIME) ge 01 and day(MODIFIEDDATETIME) ge 23 and hour(MODIFIEDDATETIME) ge 05 and minute(MODIFIEDDATETIME) ge 07 and second(MODIFIEDDATETIME) ge 59.

all results empty output with value as []. But there are rows satisfying the condition.

I see some microsoft documentation stating that datetime should be suffixed as - datetime'2021-01-23T05:07:59.5285205Z'.

There are community posts similar to breaking down of of datetime to year, month, date, hour, minute, second. But all the posts are some 2 years back and I do not aware that they still holds good.

But, none of the method worked. Can someone help me with this.?

@RezaDorrani @yoshihirok @O365_JP_Support @Pstork1 @hilary_stoupa @ScottShearer @andstr05 @Pieter_Veenstra @julieann @efialttes @yashag2255 @ToddBaginski

I have the same question (0)

All responses (20)

Answers (0)

vissvess 64 on at

Like (0)

Report

Re: SQL connector Get Rows action filter query for datetime error

There was another similar post open without a solution here: Re: MS Flow wont interpret my SQL Get Rows Filter

Was this reply helpful? Yes No
Pstork1 68,306 Most Valuable Professional on at

Like (0)

Report
Re: SQL connector Get Rows action filter query for datetime error

The first question I need to ask is what data type is the date time column in SQL? SQL has several types and they don't all work the same. But the thing I would try is to remove the single quotes from around the ISO 8601 string. I don't think ODATA queries use the quotes when comparing dates. Like this.

MODIFIEDDATETIME ge 2021-01-23T05:07:59.5285205Z

Was this reply helpful? Yes No
vissvess 64 on at

Like (0)

Report

Re: SQL connector Get Rows action filter query for datetime error

@Pstork1
For the first question, The data type of the column in SQL is 'datetime'.
I saw in one of Microsoft documentation, that -datetime'----' is to be used.

I tried with and without quotes, none gave me the correct results.

Was this reply helpful? Yes No
Pstork1 68,306 Most Valuable Professional on at

Like (0)

Report
Re: SQL connector Get Rows action filter query for datetime error

I did some testing on one of my demo databases in Azure SQL. For a field that is DateTime here is what worked for me (The field is named DateTime in my database)

If you want to filter just on the Date portion and ignore the time these both work

DateTime ge date(2020-10-13T12:00:00.000) DateTime ge date(2020-10-13T12:00:00.000Z)

If you want to filter on the whole Timestring these work

DateTime ge '2020-10-13T12:00:00.000' DateTime ge '2020-10-13T12:00:00.000Z'

Note the use of single quotes when doing the whole thing and the lack of them when not.

And if you need to adjust the target date do that using the Power Automate functions.

Here's the function for that query

formatDateTime(addDays('2020-10-13T12:00:00.000Z', 10),'yyyy-MM-ddThh:mm:ss.000')

The key is that the timestring needs to be either ISO 8601 compliant or timezone independent. SQL prefers the timezone independent format

Was this reply helpful? Yes No
vissvess 64 on at

Like (0)

Report

Re: SQL connector Get Rows action filter query for datetime error

@Pstork1 , thanks for your response.

I do not have Azure SQL. I am doing this for On-Prem SQL.
What format do you mean by ISO 8601 and timezone independent?

Could you please elaborate? However, I tried all the combinations, yet I'll give a try.

Was this reply helpful? Yes No
vissvess 64 on at

Like (0)

Report

Re: SQL connector Get Rows action filter query for datetime error

On doing all the above combinations I got the null value.
The flow is as follows:

Was this reply helpful? Yes No
Pstork1 68,306 Most Valuable Professional on at

Like (0)

Report

Re: SQL connector Get Rows action filter query for datetime error

The value inside the Date() function in your printout is in ISO8601 format. If you remove the Z at the end, which is the GMT time zone then it would be timezone independent. If you do that filter with Date() it should filter everything that isn't greater than or Equal to 1/28/2020. When using Date() timezone will be ignored. That should work with a Datetime SQL field. What version of SQL are you using? That could also be causing a problem.

Was this reply helpful? Yes No
vissvess 64 on at

Like (0)

Report

Re: SQL connector Get Rows action filter query for datetime error

Its Microsoft SQL Server 2016 (KB4577775) version 13.0.5850.14 (X64) Windows Server 2016 Standard 10.0

Was this reply helpful? Yes No
Pstork1 68,306 Most Valuable Professional on at

Like (0)

Report

Re: SQL connector Get Rows action filter query for datetime error

Sorry, but I don't know what else to suggest. From what I'm seeing the code you are using should work. I don't know what else to suggest.

Was this reply helpful? Yes No
vissvess 64 on at

Like (0)

Report

Re: SQL connector Get Rows action filter query for datetime error

@abm , Your thoughts on this.!?
One of your earlier responses in this post: Solved: SQL GetRows OData Date Filter - Power Platform Community (microsoft.com) to go for www.svenpauwels.com The link is not working.

Was this reply helpful? Yes No