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