Select to view content in your preferred language

ArcMap Definition query - Last Month, 2019 and 2020

1209
3
Jump to solution
01-14-2020 11:17 PM
timdunlevie1
New Contributor II

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

0 Kudos
1 Solution

Accepted Solutions
XanderBakker
Esri Esteemed Contributor

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

View solution in original post

3 Replies
XanderBakker
Esri Esteemed Contributor

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
New Contributor II

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

XanderBakker
Esri Esteemed Contributor

Hi timdun ,

You're welcome. Glad it works!

0 Kudos