sql - Arcpy cursors, WHERE clauses, and date/time fields

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)

Answer

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.