Pulldata query problem: unable to select specific date

[DouglasMarshall1]

Hello all

I have a pulldata function that utilizes a date field for a query. I can use orderByFields ASC and DESC successfully, but when I try a simple "and Current_date= DATE 'YYYY-MM-DD' in my concat statement, it does not work.

Doesn't work: pulldata("@layer","getValue", "attributes.variable",concat( ${URL},      "?t=", now() ), ${where} )

where: concat("Plot=",${plot}, "and Current_Date= DATE '2026-05-12' " )

I've tried many different methods involving TIMESTAMPS and Intervals to no success, so I'm starting with a simple query to narrow down the problem. Can anyone suggest why ordering would work and not the other? I suspect that caches are involved, but not sure. Thanks for any help.

Doug

[ZacharySutherby]

Hey @DouglasMarshall1, 

Try:

<date_field> = timestamp 'yyyy-mm-dd HH24:mm:ss'

This came from https://developers.arcgis.com/rest/services-reference/enterprise/query-feature-service-layer/#date-t... which is the REST API operation the pulldata("@layer") function is using. 

I would also suggest testing your where clause using the query operation on the REST API directly to see if it works there.  

[DouglasMarshall1]

Zach

Thank you for the prompt reply and advice. I have tried timestamps and current_timestamp and intervals with no luck. I have put default values in the Excel sheet (see attachment) and get blank results, so I know the pulldata function is doing something. To be honest, it's probably something incredibly simple as the program is accepting my Date field for orderByFields. I'm an ArcGIS veteran, so I'm used to answers right in front of me, likely due to my limited understanding of SQL (LOL). Anyway, at your convenience if you have the time, please take a look and let me know what you think.

[TylerGraham2]

Doug,

I've got a couple of suggestions.

1. Try converting time to the Unix epoch, most calculations involving date need to be done in Unix. I assume that same may apply here even though it's a SQL query.  I'd do the calculation in a separate field and pass it in.  Here's some info on time calculations.  

Date and time—ArcGIS Survey123 | Documentation

2. In my experience date queries need to match exactly, including the time. So, querying for date '2026-05-12' may not return anything because the fields are YYYY-MM-DD HH:MM:SS and 2026-05-12 is not an exact match if the field is 2026-05-12 12:00:00 AM.  If your data is collected date only where the time is consistently a single time, usually 12:00:00, try adding that to the query. If the times vary you may need to try the contains operator and see if that works.  

3. Also, if your data is hosted in AGOL the time zone of the data is likely going to be different than what you see.  Times are usually stored in UTC-0 in AGOL. Export your data into an excel spreadsheet and you'll see the difference when you compare them with what you see on screen. In my forms that collect date/time data I've added a UTC offset field and use pulldata to calculate the utc offset, which varies when daylight savings is in effect, so I've got a hard coded offset that I can use to adjust my times to get appropriate interactions with the hosted feature service.  

[DouglasMarshall1]

Tyler

Sorry for the late reply. I'm afraid I assumed I would get an e-mail if there were responses. I do appreciate the detailed answer you provided. Unfortunately, while you did help me better understand how time is used in Survey, especially the unseen UTC factor, my problem lies in the pulldata query format. The query system appears to be based on ISO 8601, but I am still having problems. When I figure it out, I'll be sure to post my solution. Thanks again.

[TylerGraham2]

I got beat up with date/time this week trying to use repeats to create a timeline of events for investigations. I ended up having to pass my final calculated date/times as text because datetime fields referenced in survey123 get rounded to the minute if they are part of the form at all (including hidden).  

If you can, on the layer you're querying, I'd try adding a text field. Use it to store just the date or date/time as a string and see if you can query that field successfully instead.