ArcMap Definition query - Last Month, 2019 and 2020

[timdunlevie1]

Hi guys,

Running ArcMap 10.6 and have fc's sitting in a file geodatabase.

Last year I had a set of .lyr files setup with a definition query showing activity for the 'Last Month'.

The definition query I used was this:

(EXTRACT(MONTH FROM SEARCH_DATE) = EXTRACT(MONTH FROM CURRENT_DATE)-1) AND EXTRACT(YEAR FROM SEARCH_DATE) = EXTRACT(YEAR FROM CURRENT_DATE)

This worked fine for dates January thru to December in the same year.

However, opening these .lyr files in January 2020 doesn't work correctly.

I basically have to re-design the query to something like this for December 2019 data:

(EXTRACT(MONTH FROM LAB_DATE) = EXTRACT(MONTH FROM CURRENT_DATE)+11) AND EXTRACT(YEAR FROM LAB_DATE) = EXTRACT(YEAR FROM CURRENT_DATE)-1

But then would have to change the filter again to get January 2020 data when we move into February 2020.

Is there a permanent query i can set and not have this issue for 2020 to 2021?

or am i stuck with manually changing them now, then changing again in January 2021?

thanks

[XanderBakker · Accepted solution]

Hi timdun ,

I guess you could do something like this, since the date functions available are pretty limited:

EXTRACT(YEAR FROM LAB_DATE) * 12 + EXTRACT(MONTH FROM LAB_DATE) = EXTRACT(YEAR FROM CURRENT_DATE) * 12 + EXTRACT(MONTH FROM CURRENT_DATE) - 1‍

[timdunlevie1]

Awesome ! this works...Many thanks once again Xander.

[XanderBakker]

Hi timdun ,

You're welcome. Glad it works!