I can't seem to get the following query to work using the DB2 GetRows action:
DATE(verified in the DB2 table itself)
Filterarea of the GetRows action, I type:
TEST_COLUMN gt '2020-06-01
Results in an error:
We cannot apply operator < to types Text and Date. inner exception: We cannot apply operator < to types Text and Date. clientRequestId: 51595fbd-354f-4c2c-a1bd-dba04f264b6b
TEST_COLUMN lt '2020-06-01
`TEST_COLUMN lt formatDateTime('2020-06-01', 'yyyy-MM-dd')
TEST_COLUMN eq '2020-06-01
What logic operator should I use to filter for records created after a certain date? Please advise. This is blocking me at the moment.
As the error message notes, you cannot use the greater/less than for the query on Dates and Strings. You could technically grab the date values and then convert them into ticks that you then compare inside the flow
however this will not let you use the filter in the action itself, but be something you add as a loop with condition after the get rows.
You could add a column with the integer value of the date time into your database and compare on that though.
The equal works as it is looking at at item as a constant and can recognize the value as a string/date, so its checking to see if the field equals the string value "2020-06-01" if there is extra space it it includes extra information such as day of the week or is in a different time format, it would not return a value.
Otherwise, the only other option I could suggest is checking to see if you can get the Odata filter query to work when directly interacting with the DB2 and if so, open a service request with the Support desk to see if there is any more definitive details on the filter query inside of flow.
have a good day,
Thank you for the insights @braydenlarson . I like your idea of:
`You could add a column with the integer value of the date time into your database and compare on that...`
I've successfully used ODATA filter queries where Modified > 'xxxx/xx/xx' for Sharepoint HTTP actions. Its strange to me that DB2 doesn't support this. Would be nice to know if its a limitation of DB2 itself or there is an issue with the DB2 Connector in Flow. Maybe someone from that team will run across this post.
Hm. Need to know if the DB2 connector in Flow is broken or this is intended behavior.
In Flow, a DB2_DATECOLUMN gt '2020-08-01' results in error. Strangely enough, the error is DIFFERENT now than the original error I reported above. "Http request failed: the content was not a valid JSON."
In raw SQL query: a DB2_DATECOLUMN > '2020-08-01' works great
Is ODATA-to-DB2 the issue or ?
Check out the winners of the recent 'Can You Solve These?' community challenge!
Join us for an in-depth look at the new Power Automate features and capabilities at the free Microsoft Business Applications Launch Event.
Features releasing from October 2020 through March 2021
Fill out a quick form to claim your user group badge now!