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

1129
2
Jump to solution
03-09-2021 09:33 PM
LindsayRaabe_FPCWA
Occasional Contributor III

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?

 

Lindsay Raabe
GIS Officer
Forest Products Commission WA
1 Solution

Accepted Solutions
DanPatterson
MVP Esteemed Contributor

help topic

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

Functions, Date Functions... EXTRACT

perhaps


... sort of retired...

View solution in original post

2 Replies
DanPatterson
MVP Esteemed Contributor

help topic

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

Functions, Date Functions... EXTRACT

perhaps


... sort of retired...
LindsayRaabe_FPCWA
Occasional Contributor III

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. 

Lindsay Raabe
GIS Officer
Forest Products Commission WA