Take the 2-minute tour ×

I'm having trouble making a selection of rows that meet certain date criteria using my arcpy.UpdateCursor(). I need to update records in a field in a geodatabase table with an ascending value based on the date field. Esentially, what I want is for each date on or after 6/1/2004 and before or on 9/30/2004 to have a value starting at 1 ascending to 122 (the number of days between those dates, inclusively).

The trouble seems to be with the syntax in my WHERE clause, I get a syntax error that says:

Syntax error (missing operator) in query expression '( "VAL_DATE" >= date'2004-06-01 00:00:00' AND "VAL_DATE" <= date'2004-09-30 00:00:00' )'.

Can anyone point me to the "missing operator" the error is throwing in my face??

FYI, here is the bit of code of I'm using:

import arcpy
from datetime import datetime

table = r'removed...\noaa_precip_master.mdb\noaa_precip_master'

start_sim_date = datetime.strptime('06/01/2004', '%m/%d/%Y')
end_sim_date = datetime.strptime('09/30/2004', '%m/%d/%Y')

sim_date_counter = 1

where = '"VAL_DATE" >= date\'%s\' AND "VAL_DATE" <= date\'%s\'' % (start_sim_date,end_sim_date)
## where = '"VAL_DATE" > date\'%s\'' % (start_sim_date)
print where
rows = arcpy.UpdateCursor(table,where)
for row in rows:
    date = row.VAL_DATE
    print date, sim_date_counter
    row.SIM_DATE = sim_date_counter
    sim_date_counter += 1
del rows

UPDATE: Solved, see change to "WHERE" statement below:

where = '[VAL_DATE] >= #%s# AND [VAL_DATE] <= #%s#' % (start_sim_date,end_sim_date)
share|improve this question
Forgot to note that the field "VAL_DATE" is a true date field. –  Jason Nov 21 '11 at 16:36
Did you try using a different variable name? "where" may be protected. Try "whereExpression" or something like that. –  dmahr Nov 21 '11 at 16:44
@dmahr, I've used the variable name "where" in the past with no trouble. To be sure I tested with a new variable name just now and it didn't help. Thanks though... –  Jason Nov 21 '11 at 17:35

1 Answer 1

up vote 6 down vote accepted

Does the query execute from the "Select By Attributes" dialog? Are you sure you have the right syntax for the date field.

What database are you using? Each database has its own syntax for querying dates. Take a look at the Arc10 "SQL reference for query expressions used in ArcGIS" in the DATES AND TIMES section.

share|improve this answer
Thanks for that link. I had looked at another reference in that folder in the ESRI Desktop Help Tree, but did not look at this one. I changed the field name syntax back to brackets "[]" for the personal gdb I'm using and added hashes "#" before and after the date string. Once I did these two things the script worked, thank you for the link! –  Jason Nov 21 '11 at 17:49

Your Answer


