topic Re: How to define a where clause for the 'date' data type in arcpy using cursor? in Python Questions

How to define a where clause for the 'date' data type in arcpy using cursor?

broy06 — Mon, 09 Aug 2021 10:27:06 GMT

Could anyone please help me, how to define a where clause for the date data type?. Currently using Arcmap 10.7.

below is my code:

import arcpy
arcpy.env.workspace = "D:\\Maps and GDBs\NapervilleGas.gdb"
featureClass_1 = "P_Pipes"
Table_Pipe = "Pipe_Risk"
fieldNames_1 = [ "Material" , "OPERATINGPRESSURE" , "MEASUREDLENGTH" , "INSTALLATIONDATE", "Risk_Score"]

fieldNames = ["Material" , "OPERATINGPRESSURE" , "MEASUREDLENGTHLOWERLIMIT" ,"MEASUREDLENGTHUPPERLIMIT" , "INSTALLATIONDATELOWERLIMIT" , "INSTALLATIONDATEUPPERLIMIT" , "OVERALLRISKSCORE"]

def Pipe_Risk_Score(INSTALLATIONDATE):

ID_Out_fields = ["INSTALLATIONDATELOWERLIMIT" , "INSTALLATIONDATEUPPERLIMIT" , "OVERALLRISKSCORE"]

ID_where_Clause = "(INSTALLATIONDATELOWERLIMIT <= {} and INSTALLATIONDATEUPPERLIMIT >= {})".format(INSTALLATIONDATE)

Table_Pipe_Cursor = arcpy.da.SearchCursor(Table_Pipe, ID_Out_fields, ID_where_Clause)

for row in Table_Pipe_Cursor:

lst.append(row[2])
ID_Risk_Score = row[2]
print (ID_Risk_Score)

Pipe_Risk_Score("INSTALLATIONDATE")

Re: How to define a where clause for the 'date' data type in arcpy using cursor?

Anonymous User — Mon, 09 Aug 2021 11:24:05 GMT

If the installation date is coming from the lookup table, (I assume it’s like the last question you posted with the whereclause for the length?) it I’ll be in the correct datetime format if the field is already datetime type. If it’s a string, convert it to a datetime using datetime.strptime() and use it in your query.

Your code here is passing ‘INSTALLATIONDATE’ as a string into the function instead of the assigned underlying value because you wrapped it in quotes in your method call.

Re: How to define a where clause for the 'date' data type in arcpy using cursor?

Anonymous User — Mon, 09 Aug 2021 11:45:24 GMT

I posted this on your other question, its relevant here too.

You are passing strings into your functions:

Pipe_Risk_Score("INSTALLATIONDATE")

INSTALLATIONDATE is assigned "INSTALLATIONDATE" and your whereclause is:

"INSTALLATIONDATELOWERLIMIT <= 'INSTALLATIONDATE' and INSTALLATIONDATEUPPERLIMIT >= 'INSTALLATIONDATE'"

If you want the float / date that is assigned to MEASUREDLENGTH (or any of the other values), you need to remove the quotes in the function call so it passes the variables and not "INSTALLATIONDATE" strings. For example:

INSTALLATIONDATE = datetime.strptime('08/03/2021 12:43:34') Pipe_Risk_Score(INSTALLATIONDATE)

INSTALLATIONDATE is assigned datetime 08/03/2021 12:43:34

"INSTALLATIONDATELOWERLIMIT <= datetime(08/03/2021 12:43:34) and INSTALLATIONDATEUPPERLIMIT >= datetime(08/03/2021 12:43:34)"