0

What is the ArcGIS SQL expression to select all rows containing the month of May across several years?

Desired records highlighted in Excel:
4 of 10 rows meet criteria

The source table is in a file geodatabase and I'm using ArcGIS Pro v2.2.4.

Attempts so far

I tried using the wildcard characters for strings listed in the Pro SQL reference but they (unsurprisingly) yield "invalid syntax" error:

SAMPLE_DATE = date '%-05-%'
SAMPLE_DATE = date '__-02-__'

KB article How To: Search for specific parts of a date stored in a Date-Time field offers EXTRACT(MONTH ...) = nn but that returns zero records.

EXTRACT(MONTH FROM SAMPLE_DATE) = 05

SQL valid but zero records

1

The KB article is correct, and so is the warning message after a fashion: In the Definition Query box the expression is run against the results of the preceding expression in effect when the tool was opened or last verified.

I had been entering a series of expressions one after the other narrowing in on the right solution. After clearing the expression, closing the dialog and then re-opening Definition Query I get the expected results. (Note: there is a 1 to 3 second pause after closing the dialog to when the table view is updated.)

Fresh def-query session results showing only May across many years

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.