How to build a whereClause with a datetime field in Python

1582
3
Jump to solution
03-02-2016 07:26 AM
JoseSanchez
Occasional Contributor III

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

0 Kudos
1 Solution

Accepted Solutions
AllisonMuise1
Regular Contributor

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

View solution in original post

3 Replies
AllisonMuise1
Regular Contributor

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
Occasional Contributor III

Thank you.

This syntax seems to work too:

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

Thanks

0 Kudos
JacobTodd1
New Contributor III

Hi Jose,

Be sure to mark Allison's response as correct.

Thanks

0 Kudos