1

My attribute table contains a Start date in m/dd/yyyy format. I would like to build a query that only shows data from current month forward.

Does anyone know how to build a query or expression for this problem?

1
  • at first glance, it seems you would do something like "date" > current date in the Query Builder. if there is more to it, could you expand your question.
    – ZrSiO4
    May 11 '18 at 16:26
4

The answer depends on the source of the data. If this is a file geodatabase you could use an expression like:

EXTRACT(MONTH FROM CURRENT_DATE) = EXTRACT(MONTH FROM START) AND EXTRACT(YEAR FROM CURRENT_DATE) = EXTRACT(YEAR FROM START)

The SQL you would need to use would probably be different for a personal geodatabase or Enterprise SDE geodatabase. If your data comes from those databases you should consult the help for Access or your SDE geodatabase server to create a similar query. The query above assumes your start dates are all in the present or the past or you only want dates in the current month and year and not in future months.

Probably a better alternative that works even if the Start field contains dates in future months and you want all dates from the first day of the current month forward the query should be:

START >= CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE) + 1
1

You also have the alternative of using a definition query like below, but it is not dynamic. If the field is configured as a data type: Date you could try the below method. Here are a few features with dates in Feb 2018 and May 2018.

enter image description here

I only want to show dates from May 1 to today. Keep in mind that the timestamp is also included in the Date data type. So, my definition query in the layer properties would include the time as well, like below:

Date >= date '2018-05-01 00:00:00'

Once it is applied, the results will only show the current month's (May) features.

enter image description here

The problem is, if you went back and opened the file in June and have added June features, you would now be showing both May and June results and would need to adjust the definition query for June.

2
  • 1
    Thank you, this is exactly what I need. I can easily adjust the query as needed.
    – Shea
    May 14 '18 at 14:42
  • @Shea Good to hear - please mark the post as answered if you get a chance to let others know it is resolved. Glad this worked for you!!
    – MapHound
    May 14 '18 at 15:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.