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
No comments:
Post a Comment