Skip to main content
Solved

WHERE clause in Reader

marta.podsiad
Supporter
Forum|alt.badge.img+9

Hi,

I'm attempting to filter the records in the reader using the WHERE Clause, but I'm having trouble making it work. I'm trying to select all entries created after 20230718. I've tried the following equations, but it failed.

"created_date">20230718000000.000000

"created_date" LIKE '20231718%'

 

Error 

ArcGIS Portal Feature Service Reader: Encountered an unexpected error. The error code from the server was '500' and the message was: 'Invalid data type for expression ["created_date">20230718000000.000000].'. Details: ''

 

ArcGIS Portal Feature Service Reader: Encountered an unexpected error. The error code from the server was '500' and the message was: 'Invalid data type for expression ["created_date" LIKE '20230718%'].'. Details: ''

Best answer by nielsgerrits

What I would try is to get the query right in the arcgis rest endpoint first and then move it to workbench.

ESRI’s documentation is not very clear on how to do this, but the following blog discusses this topic:

https://www.esri.com/arcgis-blog/products/api-rest/data-management/querying-feature-services-date-time-queries/

I have not tested this, but the syntax should be

<DateField> = DATE 'YYYY-MM-DD'

<DateField> = TIMESTAMP 'YYYY-MM-DD HH:MI:SS'

So something like

created_date > DATE '2023-07-18'

should work.

View original
    Did this help you find an answer to your question?

    2 replies

    david_r
    Evangelist
    • david_rEvangelist
    • March 5, 2024

    You cannot use the FME datetime format with ArcGIS Portal, try one of the supported formats described here: https://enterprise.arcgis.com/en/portal/latest/use/work-with-date-fields.htm

    marta.podsiad
    nielsgerrits
    2 people upvoted this
    • marta.podsiad
      marta.podsiad
    • nielsgerrits
      nielsgerrits
    nielsgerrits
    VIP
    Forum|alt.badge.img+53
    • nielsgerritsVIP
    • Best Answer
    • March 5, 2024

    What I would try is to get the query right in the arcgis rest endpoint first and then move it to workbench.

    ESRI’s documentation is not very clear on how to do this, but the following blog discusses this topic:

    https://www.esri.com/arcgis-blog/products/api-rest/data-management/querying-feature-services-date-time-queries/

    I have not tested this, but the syntax should be

    <DateField> = DATE 'YYYY-MM-DD'

    <DateField> = TIMESTAMP 'YYYY-MM-DD HH:MI:SS'

    So something like

    created_date > DATE '2023-07-18'

    should work.

    ctredinnick
    1 person upvoted this
    • ctredinnick
      ctredinnick

    Reply


    Helpful Members This Week

    Community Stats

    30,971
    Posts
    117,386
    Replies
    38,754
    Members

    Latest FME

    Terms & ConditionsCookie settings

    Cookie policy

    We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

     
    Cookie settings
    A product of
    Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.