Use SQL query to get particular month between years

122
4
09-27-2022 11:50 PM
Vakhtang_Zubiashvili
Occasional Contributor II

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

0 Kudos
4 Replies
jcarlson
MVP Honored Contributor

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.

- Josh Carlson
Kendall County GIS
Vakhtang_Zubiashvili
Occasional Contributor II

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.

0 Kudos
jcarlson
MVP Honored Contributor

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

- Josh Carlson
Kendall County GIS
0 Kudos
JeffreyWilkerson
Occasional Contributor III

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... 

Which uses the SQL:

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

 

0 Kudos