query Date Range syntax

[jaykapalczynski]

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) + "' ";

[RobertScheitlin__GISP]

Jay,

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

[jaykapalczynski]

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

[Drew]

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

[jaykapalczynski]

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

[RobertKirkwood]

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?  

[RobertScheitlin__GISP · Accepted solution]

@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

[RobertKirkwood]

that worked perfectly! Thank you!

[RobertScheitlin__GISP · Accepted solution]

And here is how I do most of mine:

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

[jaykapalczynski]

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

(dom.byId("StartDate").value

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