Hi Brains Trust. I'm trying to build a definition query (SQL) that looks a little like this;
fih_fire_type IN ('WF') And fih_year1 >= (YEAR(CURRENT_DATE())-20)
where fih_year1 is a short integer with a year in it (i.e. 2019, 2020, etc). I want to be able to filter my data by features that have a Year value within the last 20 years.
I believe the CURRENT_DATE function works, but it looks like YEAR() isn't available in the definition query SQL window, meaning I can't extract the current Year as an integer. I've tried a few others and keep having the same problem (limited SQL commands available).
Any tips on how to make this formula work?
Solved! Go to Solution.
help topic
SQL reference for query expressions used in ArcGIS—ArcGIS Pro | Documentation
Functions, Date Functions... EXTRACT
perhaps
help topic
SQL reference for query expressions used in ArcGIS—ArcGIS Pro | Documentation
Functions, Date Functions... EXTRACT
perhaps
Hi @DanPatterson . Thanks for the link. I think I had seen that page already and had tried the Extract function earlier but I must have been using it wrong. I tried again just now to make sure before posting, and just as well as I got it to work!
(fih_fire_type IN ('WF') And fih_year1 >= (EXTRACT(YEAR FROM CURRENT_DATE())-20)) Or (fih_fire_type = 'PB' And fih_year1 >= (EXTRACT(YEAR FROM CURRENT_DATE())-2))
Formula now returns wildfires within the last 20 years and prescribed burns in the last 2.