Using Python to select records by date field

Question

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

• Midavalo: before trying to calculate the difference and pass that as your SQL, have you tried your created_date >= somestaticdate and checking that part works?
• Midavalo: How is your data stored?
• Tom: Then it looks like you just need single quotes around the time and not around the field name.
• Vince: It is important to understand that ArcGIS is NOT executing the query -- The database is. You must format the WHERE clause as it will be used by the database.
• Tom: @rralbritton I don't think that link means what you think it means.

Answer 1

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)

• Midavalo: SQL = "created_date >= '{}'".format(report_time.strftime('%Y-%m-%d %H:%M:%S'))
• Tom: This is why you should never concatenate strings with +.
• yanes: great! however check with arcpy.GetCount_management('ViewTable') , to see if you've correctly selected records. or add print arcpy.GetMessages() after your select by attribute line. To check what it did. Not that I am saying it doesn't work, just wondering because I didn't think specifying field without quotations worked. Also please accept your answer so that people could see the Q has an accepted answer.
• Tom: Interesting, because your solution is exactly what I said earlier: "Then it looks like you just need single quotes around the time and not around the field name." Maybe it wasn't clear that I was referring to quotes within the SQL query itself.
• reactFullStackDeveloper: @yanes - good point on using the GetCount tool - I did use that to check the records being selected, I just didn't include that info here - but its useful advice for anyone in the same/similar situation

Answer 2

Try wrapping your Where statement with three double quotations so that Arcpy understands it as an expression for your select function not a standalone SQL task - I may be wrong on the reasoning, I will check.

SQL = """ "created_date" >= '{}'""".format(report_time.strftime('%Y-%m-%d %H:%M:%S')) 
arcpy.SelectLayerByAttribute_management(ViewTable,"NEW_SELECTION", SQL)

Also why do you have two operands in the SQL?

• reactFullStackDeveloper: Yanes - thanks for the reply but its still doesn't work - By adding all of the quotes its reading the entire SQL as a string which is incorrect: SQL = """ "created_date >= " report_time.strftime('%m/%d/%Y')""" returns this as the SQL statement: "created_date >= " report_time.strftime('%m/%d/%Y'). Also - someone asked why I had a double operand - that was just a mistake type. Sorry I should have proofed that better
• yanes: Help me understand the SQL isn't it supposed to be a simple statement where: you are looking at a field named "Created_date" and selecting all the records that are later or on the date specified by your variable report_time.strftime('%m/%d/%Y'). In that case this statement should work. 2 things. 1. please confirm created_date is your field, 2. confirm that the variable report_time.strftime('%m/%d/%Y') doesn't give you error, when run on its own.
• yanes: Also Vince's comment under the original post is valid, check in a GIS software how exactly the time/date is formatted in your table your report_time.strftime('%m/%d/%Y') need to reproduce the exact format. You can also use searchcursor to print your records out in python and see the format.
• reactFullStackDeveloper: Thank you for your input. I figured it out. Please see my post below
• yanes: @Midavalo, thanks! for the correction, it was a mistake! I have edited it now. I didn't know about the second option, good to know. It looks less of a coloring book without the triple double quotes :). I am not sure if I remember correctly - there is an unimaginative alternative that goes as - /"created_date >= '{}'/".format(report_time.strftime('%Y-%m-%d %H:%M:%S'))