Using ArcGIS definition query for dates?

Question

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?

Answer 1

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

Answer 2

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.

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.

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.