Use Short Integer attribute Year (YYYY) in definition query referencing CURRENT_DATE

[LindsayRaabe_FPCWA]

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

LindsayRaabe_FPCWA_0-1615354262532.png

Any tips on how to make this formula work?

[DanPatterson · Accepted solution]

help topic

SQL reference for query expressions used in ArcGIS—ArcGIS Pro | Documentation

Functions, Date Functions... EXTRACT

perhaps

[LindsayRaabe_FPCWA]

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.