topic Re: query Date Range syntax in ArcGIS JavaScript Maps SDK Questions

query Date Range syntax

jaykapalczynski — Mon, 03 Apr 2017 19:04:11 GMT

Wondering if anyone can help with the syntax to query a date range?

query.where = "County_Nam = '" + (dom.byId("CountyName").value) + "' AND Squad = '" + (dom.byId("Squad").value) + "' ";

query.where = "County_Nam = '" + (dom.byId("CountyName").value) + "' AND Squad = '" + (dom.byId("Squad").value) + "' AND DateIncident = '" + (dom.byId("StartDate").value) + "' BETWEEN DateIncident = '" + (dom.byId("EndDate").value) + "' ";

Re: query Date Range syntax

RobertScheitlin__GISP — Mon, 03 Apr 2017 19:19:11 GMT

Jay,

What is the underlying data database type (SQL Server, Shapefile, FGDB, Oracle, AGOL)?

Re: query Date Range syntax

Drew — Mon, 03 Apr 2017 19:27:14 GMT

We use MS SQL Server and we query our dates like so..

SAMPLE

query.where = "CreatedDateTime >= '2017/03/27 00:00:00 AM' AND CreatedDateTime <= '2017/04/03 11:59:59 PM'"‍

Sometimes its easier to test these things on the REST Endpoint too..

Drew

Re: query Date Range syntax

jaykapalczynski — Mon, 03 Apr 2017 19:37:17 GMT

SQL Server...This query works fine....just need the Date Range

Re: query Date Range syntax

RobertScheitlin__GISP — Mon, 03 Apr 2017 19:39:45 GMT

And here is how I do most of mine:

query.where = "DateIncident BETWEEN timestamp '" + dom.byId("StartDate").value + "' AND timestamp '" + dom.byId("EndDate").value + "'"‍‍‍‍‍

Re: query Date Range syntax

jaykapalczynski — Mon, 03 Apr 2017 20:21:50 GMT

Thank Andrew....your solution worked fine...

Re: query Date Range syntax

jaykapalczynski — Sat, 11 Dec 2021 08:00:12 GMT

Had to add two ) after value for start and end date but worked great

(dom.byId("StartDate").value

(dom.byId("StartDate").value)

Re: query Date Range syntax

RobertKirkwood — Wed, 15 Jun 2022 18:57:05 GMT

How would i query value less than todays date? i was trying something like

query.where = GETDATE() >= "EXPIRATION_DATE"

But this is not working. Any thoughts?

Re: query Date Range syntax

RobertScheitlin__GISP — Wed, 15 Jun 2022 19:39:50 GMT

@RobertKirkwood GETDATE() is a SQL server function if you want the date from JS code then it would look like this:

query.where = "'" + new Date().toJSON().slice(0,10).replace(/-/g,'/') + "' >= EXPIRATION_DATE";

The current date will look like "2022/06/15" based on the JS code above

Re: query Date Range syntax

RobertKirkwood — Wed, 15 Jun 2022 19:55:41 GMT

that worked perfectly! Thank you!