date between query not working

[AmeWunderle]

Hi all,

I am accessing the REST API using Python to auto-run and download feature reports from Survey123. This works beautifully when I run it daily, however, as there are times when we have power outages, I wanted it to run based on the last edit date and today (so querying all surveys within a range). 

My original query is formatted (which works in my script):

starttime = datetime.datetime.now().strftime('%Y-%m-%d')

"{\"where\":\"EditDate >= DATE \'"+starttime+"'\"}"

printed it looks like {"where":"EditDate >= DATE '2021-06-28'"}

When I run my script I write the starttime to a json. The next time the script runs it grabs the starttime+1 day (so I don't get multiple entries). So based on the Date Blog I read using REST API @(https://www.esri.com/arcgis-blog/products/api-rest/data-management/querying-feature-services-date-ti...) 

Query 2 =

"{\"where\":\"EditDate BETWEEN DATE \'"+strlastPlusOne+"\' AND DATE \'"+starttime+"\'\"}"

which printed looks like {"where":"EditDate BETWEEN DATE '2021-06-28' AND DATE '2021-06-29'"}

but it isn't working though after the Dev conference when I was working on it and testing with other ranges, I swear it did....anyone see anything obvious as to why this query wouldn't work? I only receive the error that there is no record in the query result although there is a survey submitted for 2021-06-28.  I also tested this using different test dates widening the range from 2021-06-27 to 2021-06-29, same result. When I run with the original query for the date '2021-06-28' it works.

[AmeWunderle · Accepted solution]

Figured it out. BETWEEN is not inclusive but exclusive.