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?

  • 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
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.

  • 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
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

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.