How to build a whereClause with a datetime field in Python

[JoseSanchez]

Hello everyone,

What is the syntax to use a date field in a whereClause?

This code does not work:

datetimeVal = rowYesterday.getValue("DIGIDATE")
       
whereClause = "DateAGMT = " + datetimeVal                          ==============   This syntax does not work

srcToday = arcpy.SearchCursor(AGREEMENT, whereClause)

Thank you

[AllisonMuise1 · Accepted solution]

Hi Jose,

You code above isn't working because it tries to concatenate values of two different types - a string and a datetime. You also need to explicitly tell the query that the value of datetimeVal is a datetime so that it gets read correctly.

Try this syntax: whereClause = "DateAGMT = date'{}'".format(datetimeVal)

One way to figure out the format for these queries (if Geonet isn't fast enough!) is to run a tool like Make Feature Layer with the same query you are trying to build. Dragging and dropping the result into the Python window in ArcMap or ArcCatalog will show you the syntax and .format will let you include variables in the query without worrying about variable types.

-Allison

[JoseSanchez]

Thank you.

This syntax seems to work too:

whereClause = '"AGMID" =  %s AND "DIGIDATE" = date \'%s\' '  % (agreementID, datetimeVal)

Thanks

[JacobTodd1]

Hi Jose,

Be sure to mark Allison's response as correct.

Thanks