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)