[GIS] Using Python to select records by date field

arcpydatetimeenterprise-geodatabaseerror-000358sql server

I am trying to write a python script to select records created within the past 7 hours (date field – database time) using a where-clause within arcpy. The table is ArcSDE database (Microsoft SQL Server).

I've written a script that calculates the time 7 hours prior to the current time. Then I want to use the "report_time" variable to select the relevant records in a table view but I'm getting an invalid expression error. I've tried to reformat the SQL statement in every way I could think of and still get the invalid expression error (I've kept them in my script and commented them out for reference).

-- Get start and end times for report

start_time= datetime.timedelta(hours = 7)
end_time = datetime.datetime.now()

report_time = end_time-start_time #this is the time that gets used to filter records

-- find all records that are later than or = to report_time

SQL = "created_date >= " + report_time.strftime('%m/%d/%Y')
print SQL

arcpy.SelectLayerByAttribute_management(ViewTable,"NEW_SELECTION", SQL)

-- SQL = "created_date >= " + str(report_time.strftime('%m/%d/%Y'))

-- SQL = '"created_date"<='+ report_time.strftime('%Y/%m/%d')

-- SQL = '"created_date"<='+ report_time.strftime('%m/%d/%Y')

-- SQL = "'created_date'<= "+ report_time.strftime('%m/%d/%Y')

-- SQL = '"created_date"<='+ report_time.strftime('%m/%d/%Y %I:%M:%S %p') - ExecuteError: ERROR 000358: Invalid expression

-- SQL = "'created_date'<= "+ report_time.strftime('%m/%d/%Y %I:%M:%S %p') - ExecuteError: ERROR 000358: Invalid expression

-- SQL = "'created_date'<= "+ str(report_time.strftime('%m/%d/%Y %I:%M:%S %p'))

-- SQL = '"created_date"<='+ str(report_time.strftime('%m/%d/%Y %I:%M:%S %p'))

--SQL = '"created_date"<= report_time.strftime'('%m/%d/%Y %I:%M:%S %p') - TypeError: 'str' object is not callable

--SQL = '"created_date" <= report_time' #this returns an expression error

--SQL = "'created_date' <= report_time" #also tried this - expression error

--SQL = 'created_date'<= report_time # returns error: TypeError: can't compare datetime.datetime to str

Best Answer

So I figured it out - @Yanes was close but instead of three sets of double quotes, it needed to be double-single-double. So the following script works:

-- Turn ReportsTable into a View Table

arcpy.MakeTableView_management(ReportsTable, ViewTable)

-- Get start and end times for report

start_time= datetime.timedelta(hours = 24)

end_time = datetime.datetime.now()

report_time = end_time-start_time #this is the time that gets used to filter records

-- find all records that are later than or = to report_time

SQL = "created_date >="+ "'"+report_time.strftime('%Y-%m-%d %H:%M:%S')+"'"

print SQL

-- select records within the specified time range using arcpy
arcpy.SelectLayerByAttribute_management(ViewTable,"NEW_SELECTION", SQL)
Related Question