How do you construct a definition query to return only those features in the current month without hard coding the date?

Question

I have a feature class containing a date field within a 9.3.1 File Geodatabase. There are "duplicate" features contained within this layer, one feature for each month over the past few months.

However, the final dataset may contain dates for the last few years.

| Feature 1 | '2012-05-01' |
| Feature 1 | '2012-04-13' |
| Feature 1 | '2012-03-23' |
| Feature 1 | '2011-03-23' |
| Feature 2 | '2012-05-12' |
| Feature 2 | '2012-04-14' |
| Feature 2 | '2012-03-30' |
| Feature 2 | '2011-03-30' |
... and so on

I want to apply a definition query such that the map will only display features for the current month and year and I do not want to update the definition query each month.

I have attempted to use a combination of date, CURRENT_DATE, EXTRACT, and CONCAT but without much success. Ultimately I think I need a definition query that looks something like this but with the hard-coded dates removed.

DateField >= date '2012-05-01' AND DateField <= date '2012-05-31'

CURRENT_DATE returns the current date and can replace the second component to give:

DateField >= date '2012-05-01' AND DateField <= CURRENT_DATE

However I cannot seem to format the first component such that it always starts at the beginning of the current month. I attempted to format the date string via the CONCAT but I am unable to form valid SQL and I am not sure what the error is. Ignoring the CURRENT_DATE and EXTRACT methods for now gives me this invalid SQL:

DateField >= date CONCAT(CONCAT(CONCAT('2012', '-'), CONCAT('05', '-')), '01')

Ultimately the year and month would be replace by the current year and month using EXTRACT and CURRENT_DATE. However the "simplified" SQL above fails with a generic "An invalid SQL statement was used." error. I know I am doing something wrong but I am not sure what it is.

So, how do you construct a definition query to return only those features in the current month without hard coding the date in ArcMap 9.3.1 in a 9.3.1 File Geodatabase?

Answer 1

Based on this, you can also do:

EXTRACT(MONTH from DateField) = EXTRACT(MONTH from CURRENT_DATE) AND EXTRACT(YEAR from DateField) = EXTRACT(YEAR from CURRENT_DATE)

Or a shorter solution that would also work is:

DateField >= CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE)

Answer 2

The following will do what you want, providing that your date field is of "date" data type:

EXTRACT(MONTH FROM "your_date_field" ) = EXTRACT(MONTH FROM CURRENT_DATE)