Hoppa till huvudinnehållet

Query Layer: Get objects which were created the day before

Yannik Bäder

I want to query the objects which where created the day before. In my VertiGIS Studio Mobile app there is a workflow which creates points automatically. In this process the timestamp is entered in a esriFieldTypeDate-field, in this case called DateField. 

For now I use a Query Layer activity. I thought of using a where-clause like: =DateField = (CURRENT_DATE - INTERVAL \'1\' DAY).

I tried to use ANSI-SQL. There is no error using this clause in the ArcGIS-Rest-Service. I run an Oracle Database in which the feature class is stored.

As you can see, I tried escaping as well as using " , ' and with and without = in the beginning. What is wrong with this statement?

Some further information about which database language must be used and if there are syntax differences between ArcGIS Rest Service and VertiGIS Studio Workflow Queries would be very helpful.

Best regards,
Yannik

1

Kommentarer

7 kommentarer

Datum Röster
  • Nico Burgerhart

    What is the setting for Standardized Queries on your ArcGIS Server? I could be the INTERVAL function is not allowed.

    https://enterprise.arcgis.com/en/server/latest/administer/windows/about-standardized-queries.htm

     

    1
  • Yannik Bäder
    Redigerad

    Hi Nico,

    thank you for the quick reply.

    Standardized Queries is set on true. But why don't occur errors in the REST-Service with the same where-clause?

    Is there an alternative to INTERVAL?

    Edit: I cannot change the property of Standardized Queries.

    0
  • Nico Burgerhart

    See https://community.esri.com/t5/arcgis-pro-questions/definition-query-with-dates/td-p/1074642 for a suggestion. 

     

    1
  • Yannik Bäder

    DATEFIELD = (CURRENT_DATE - 1) worked. But the DATEFIELD contains Hours, Minutes and Seconds. I didn'T find a way to properly convert datetime to date in order to compare the days.

    In Oracle I could use TRUNC() but ArcGIS Server won't allow this.

    0
  • Berend Veldkamp

    You can't compare just the date, so you should be querying an interval, e.g. something like: DATEFIELD > (CURRENT_DATE - 1) AND DATEFIELD < CURRENT_DATE

    1
  • Zack Robison

    I hate querying dates through Server.  It's like a box of chocolates in that you never know what you're gonna get.  It's not like a box of chocolates in that I actually like boxes of chocolates. 

    Yannik Bäder , please let us know when you figure out a solution so I can bookmark this for the next time I need to touch server with an Oracle backend.

    0
  • Yannik Bäder

    I don't have a solution yet.

    By now I use this: DATEFIELD > (CURRENT_DATE - 2)

     

    0

Du måste logga in om du vill lämna en kommentar.

Hittade du inte vad du sökte?

Nytt inlägg