topic Re: Use SQL query to get particular month between years in ArcGIS JavaScript Maps SDK Questions

Use SQL query to get particular month between years

Vakhtang_Zubiashvili — Wed, 28 Sep 2022 06:50:21 GMT

Hi guys, I am trying to get particular month (e.g. January) data between 2015-2018 years. I am using query:

monacemTa_Segrovebis_TariRi > '2015-01-01' AND monacemTa_Segrovebis_TariRi < '2019-01-01' AND month( monacemTa_Segrovebis_TariRi )=1

It Is working on GIS desktop version, but on web i can not use it. On web it work till "month" block, when i add "month it stops working and i get message "Invalid Query". What i did wrong?

Thanks

Re: Use SQL query to get particular month between years

jcarlson — Wed, 28 Sep 2022 11:59:14 GMT

The kinds of SQL functions available to you online are not always what you find in the desktop. See this page for reference: https://doc.arcgis.com/en/arcgis-online/reference/sql-agol.htm

In particular, the month function isn't an option, but you can instead use extract(month from monacemTa_Segrovebis_TariRi) to get the same thing.

Re: Use SQL query to get particular month between years

Vakhtang_Zubiashvili — Thu, 29 Sep 2022 07:59:21 GMT

Hi,

This is not query i want it does not work for me. In the filed "monacemTa_Segrovebis_TariRi" i have all months and i want to get data only for e.g. March or April, using this query i can not perform this action. Extract gives me only month, not its data.

Re: Use SQL query to get particular month between years

jcarlson — Thu, 29 Sep 2022 13:15:24 GMT

I wasn't giving you the full query, merely pointing out the proper SQL syntax for the part that pulls out the month.

Re: Use SQL query to get particular month between years

JeffreyWilkerson — Mon, 03 Oct 2022 15:48:59 GMT

Is this resolved by JCarlson's response? I think the EXTRACT function will give you what you what. If I query an Esri site for a Date and a Month range I get data results, as in this example:

https://services3.arcgis.com/GVgbJbqm8hXASVYi/arcgis/rest/services/LA_County_Parcels/FeatureServer/0/query?where=SpatialChangeDate+%3E+%271%2F1%2F2020%27+AND+EXTRACT%28MONTH+FROM+SpatialChangeDate%29+IN+%282%2C3%29&objectIds=&time=&geometry=&geometryType=esriGeometryEnvelope&inSR=&spatialRel=esriSpatialRelIntersects&resultType=none&distance=0.0&units=esriSRUnit_Meter&relationParam=&returnGeodetic=false&outFields=SpatialChangeDate&returnGeometry=false&returnCentroid=false&featureEncoding=esriDefault&multipatchOption=xyFootprint&maxAllowableOffset=&geometryPrecision=&outSR=&defaultSR=&datumTransformation=&applyVCSProjection=false&returnIdsOnly=false&returnUniqueIdsOnly=false&returnCountOnly=false&returnExtentOnly=false&returnQueryGeometry=false&returnDistinctValues=false&cacheHint=false&orderByFields=&groupByFieldsForStatistics=&outStatistics=&having=&resultOffset=&resultRecordCount=&returnZ=false&returnM=false&returnExceededLimitFeatures=true&quantizationParameters=&sqlFormat=none&f=html&token=

Which uses the SQL:

SpatialChangeDate > '1/1/2020' AND EXTRACT(MONTH FROM SpatialChangeDate) IN (2,3)