topic Re: ArcMap Definition query - Last Month, 2019 and 2020 in Geodatabase Questions

ArcMap Definition query - Last Month, 2019 and 2020

timdunlevie1 — Wed, 15 Jan 2020 07:17:27 GMT

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 — Wed, 15 Jan 2020 15:53:24 GMT

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 — Thu, 16 Jan 2020 01:30:54 GMT

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

XanderBakker — Thu, 16 Jan 2020 02:57:44 GMT

You're welcome. Glad it works!