cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ericonline
Community Champion
Community Champion

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

5 REPLIES 5
braydenlarson
Community Support
Community Support

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,

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

 

 

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

 

ericonline
Community Champion
Community Champion

Bump. Is gt, ge, lt, le supported by the DB2 connector? If NOT, please reply here and update the documentation to let us know. 

ericonline
Community Champion
Community Champion

I even had our DBA add a Unix Timestamp column (Decimal(15,2) to the DB2 table and I'm still not able to correctly filter using gt, ge, lt, le!


Whats up with this connector?

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Solution Authors
Top Kudoed Authors
Users online (1,246)