I am trying to select features from now back to the last 180 days. I am getting an error on the expression. I am not sure but i might be going about it the wrong way. thoughts, suggestions?
import arcpy
import datetime
#from datetime import datetime
from datetime import timedelta
fc = r"C:\Temp\BuildingPermit.gdb\ParcelData"
arcpy.MakeFeatureLayer_management(fc, "PT_FC")
start_time= datetime.timedelta(days = 180)
end_time = datetime.datetime.now()
report_time = end_time-start_time
SQL = "created_date >="+ "'"+report_time.strftime('%Y-%m-%d %H:%M:%S')+"'"
arcpy.SelectLayerByAttribute_management("PT_FC","NEW_SELECTION", SQL)
arcpy.FeatureClassToFeatureClass_conversion("PT_FC", "C:\Temp", "BP_TestA")
Solved! Go to Solution.
Delete lines 9, 10, 12 and 13 from your script. I had posted code pasted from the IDE; I should have cleaned it a bit. Your code should look something like this (but I did not test it):
import arcpy
import datetime as DT
fc = r"C:\Temp\BuildingPermit.gdb\ParcelData"
arcpy.MakeFeatureLayer_management(fc, "PT_FC")
from_date = DT.date.today() - DT.timedelta(days=180)
SQL = "created_date >= DATE '{}'".format(from_date.strftime('%Y-%m-%d %H:%M:%S'))
arcpy.SelectLayerByAttribute_management("PT_FC","NEW_SELECTION", SQL)
arcpy.FeatureClassToFeatureClass_conversion("PT_FC", "C:\Temp", "BP_TestA")
I've used something like:
>>> import datetime as DT
>>> from_date = DT.date.today() - DT.timedelta(days=180)
>>> from_date
datetime.date(2017, 11, 18)
>>> SQL = "created_date >= '{}'".format(from_date.strftime('%Y-%m-%d %H:%M:%S'))
>>> SQL
"created_date >= '2017-11-18 00:00:00'"
# sometimes you need to indicate type is DATE
# SQL = "created_date >= DATE '{}'".format(from_date.strftime('%Y-%m-%d %H:%M:%S'))
I am still getting an invalid expression even with DATE.
import arcpy
import datetime as DT
import datetime
fc = r"C:\Temp\BuildingPermit.gdb\ParcelData"
arcpy.MakeFeatureLayer_management(fc, "PT_FC")
from_date = DT.date.today() - DT.timedelta(days=180)
from_date
datetime.date(2017, 11, 18)
SQL = "created_date >= DATE '{}'".format(from_date.strftime('%Y-%m-%d %H:%M:%S'))
SQL
"created_date >= '2017-11-18 00:00:00'"
arcpy.SelectLayerByAttribute_management("PT_FC","NEW_SELECTION", SQL)
arcpy.FeatureClassToFeatureClass_conversion("PT_FC", "C:\Temp", "BP_TestA")
# sometimes you need to indicate type is DATE
# SQL = "created_date >= DATE '{}'".format(from_date.strftime('%Y-%m-%d %H:%M:%S'))
Delete lines 9, 10, 12 and 13 from your script. I had posted code pasted from the IDE; I should have cleaned it a bit. Your code should look something like this (but I did not test it):
import arcpy
import datetime as DT
fc = r"C:\Temp\BuildingPermit.gdb\ParcelData"
arcpy.MakeFeatureLayer_management(fc, "PT_FC")
from_date = DT.date.today() - DT.timedelta(days=180)
SQL = "created_date >= DATE '{}'".format(from_date.strftime('%Y-%m-%d %H:%M:%S'))
arcpy.SelectLayerByAttribute_management("PT_FC","NEW_SELECTION", SQL)
arcpy.FeatureClassToFeatureClass_conversion("PT_FC", "C:\Temp", "BP_TestA")
Specifying DATE works with ArcMap's file geodatabases and some other SQL versions. Some versions don't like/use DATE. If you get an error message, post the text of the message as that will help in getting to a solution. It may also help to know what type of geodatabase you are using. Hope this helps.
with DATE or with out DATE in line 10 i get the same error.
import arcpy
import datetime as DT
#import datetime
fc = r"C:\Temp\BuildingPermit.gdb\ParcelData"
arcpy.MakeFeatureLayer_management(fc, "PT_FC")
from_date = DT.date.today() - DT.timedelta(days=180)
SQL = "created_date >= DATE '{}'".format(from_date.strftime('%Y-%m-%d %H:%M:%S'))
arcpy.SelectLayerByAttribute_management("PT_FC","NEW_SELECTION", SQL)
arcpy.FeatureClassToFeatureClass_conversion("PT_FC", "C:\Temp", "BP_TestA")
Traceback (most recent call last):
File "D:\GIS Folder\Python Scripts\WorkingScripts\SelectByDate\SelectByDate4.py", line 12, in <module>
arcpy.SelectLayerByAttribute_management("PT_FC","NEW_SELECTION", SQL)
File "C:\Program Files (x86)\ArcGIS\Desktop10.5\ArcPy\arcpy\management.py", line 7744, in SelectLayerByAttribute
raise e
ExecuteError: ERROR 000358: Invalid expression
Failed to execute (SelectLayerByAttribute).
From your code, it looks like you are working with a file geodatabase. I would (as Mitch Holley is suggesting) confirm that the field "created_date" exists in the geodatabase and is a date type.
I was able to generate an ERROR 000358 by misspelling the date's field name or by using a non-date field in SQL where clause.
ya i had the wrong filed.
See here for reference: SQL reference for query expressions used in ArcGIS—ArcGIS Pro | ArcGIS Desktop
Is the field you're trying to query a Date type?
Have you tried just using ArcMap and 'Select By Attributes' tool to see what the unique values are.