topic Re: Use Short Integer attribute Year (YYYY) in definition query referencing CURRENT_DATE in ArcGIS Pro Questions

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

LindsayRaabe_FPCWA — Wed, 10 Mar 2021 05:33:17 GMT

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?

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

DanPatterson — Wed, 10 Mar 2021 09:36:52 GMT

help topic

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

Functions, Date Functions... EXTRACT

perhaps

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

LindsayRaabe_FPCWA — Thu, 11 Mar 2021 00:42:05 GMT

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.