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")
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)"