Monday 27 May 2019

arcpy - Using Python to select records by date field


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

arcpy - Changing output name when exporting data driven pages to JPG?

Is there a way to save the output JPG, changing the output file name to the page name, instead of page number? I mean changing the script fo...