Using Python to select records by date field

RachelAlbritton

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)

RachelAlbritton

I figured it out. I opened out the select by attribute tool in arcmap, selected the 'date_created' field and then clicked on get records so I could see how the date-time format looked in the SQL calculator compared to how it looked in the table. They were different! Using this information I reconstructed my where clause:

#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

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

Note that the quotes before report_time are double- single - double quotes " ' "

Same with the very last set of quotes