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 rows.updateRow(row) 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)