cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Super User
Super User

DB2 GetRows + Filter By Date

I can't seem to get the following query to work using the DB2 GetRows action:

Scenario:

  1. Database: DB2
  2. Connector DB2
  3. Action Get Rows
  4. Column name TEST_COLUMN
  5. Column data type DATE (verified in the DB2 table itself)

Issue:

  1. In the Filter area 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

Also tried:
TEST_COLUMN lt '2020-06-01

  • Also fails

`TEST_COLUMN lt formatDateTime('2020-06-01', 'yyyy-MM-dd')

  • Also fails

TEST_COLUMN eq '2020-06-01

  • Strangely enough, THIS SUCCEEDS, but ALWAYS returns a blank record even though i've verified there are records in the DB with 2020-06-01 in the database!

What logic operator should I use to filter for records created after a certain date? Please advise. This is blocking me at the moment.

Thank you

3 REPLIES 3
Highlighted
Community Support
Community Support

Re: DB2 GetRows + Filter By Date

Hello Ericonline,

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

braydenlarson_0-1596651449830.png

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,

Highlighted
Super User
Super User

Re: DB2 GetRows + Filter By Date

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. 

Thanks again

 

 

Highlighted
Super User
Super User

Re: DB2 GetRows + Filter By Date

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

ericonline_0-1598289568455.png

In raw SQL query: a DB2_DATECOLUMN > '2020-08-01' works great

 

Is ODATA-to-DB2 the issue or ?

Thank you

 

Helpful resources

Announcements
secondImage

August 2020 CYST Winners!

Check out the winners of the recent 'Can You Solve These?' community challenge!

thirdImage

Experience what's new for Power Automate

Join us for an in-depth look at the new Power Automate features and capabilities at the free Microsoft Business Applications Launch Event.

firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

Top Solution Authors
Top Kudoed Authors
Users online (4,256)