AnsweredAssumed Answered

Using Python to select records by date field

Question asked by rralbritton on Feb 5, 2016
Latest reply on Feb 5, 2016 by rralbritton

I am trying to write a python script to select records created within the past 7 hours (date field - database time).

I've written a script that calculates the time 7 hours prior to the current time:

(Python 2.7.8)

 

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

 

Then I wanted to use the "report_time" variable to select the relevant records in a table view but I'm getting an invalid expression error. I'm not sure if the error is because the SQL expression is incorrect (which is possible) OR if its because there's formatting differences in the date/time structure. For example if I run the above expressions in python shell, the datetime returned is 2016-02-05 03:49:11.322000

 

The date field in the SDE table is formatted like this:  2/4/2016 2:18:25 PM. I wondering if this matters and if so, how to deal with it.

 

Here's my current SQL script:

 

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

print SQL

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

Outcomes