topic Re: How to build a whereClause with a datetime field in Python in Python Questions

How to build a whereClause with a datetime field in Python

JoseSanchez — Wed, 02 Mar 2016 15:26:44 GMT

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

Re: How to build a whereClause with a datetime field in Python

AllisonMuise1 — Wed, 02 Mar 2016 16:23:45 GMT

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

Re: How to build a whereClause with a datetime field in Python

JoseSanchez — Wed, 02 Mar 2016 17:44:50 GMT

Thank you.

This syntax seems to work too:

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

Thanks

Re: How to build a whereClause with a datetime field in Python

JacobTodd1 — Wed, 02 Mar 2016 18:39:05 GMT

Hi Jose,

Be sure to mark Allison's response as correct.

Thanks