6

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)
  • 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
6

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.

  • 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

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.