Query date range

1189
8
Jump to solution
05-17-2018 10:23 AM
CCWeedcontrol
Regular Contributor

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")

0 Kudos
1 Solution

Accepted Solutions
RandyBurton
MVP Regular Contributor

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")

View solution in original post

8 Replies
RandyBurton
MVP Regular Contributor

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'))
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍
CCWeedcontrol
Regular Contributor

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'))
0 Kudos
RandyBurton
MVP Regular Contributor

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")
RandyBurton
MVP Regular Contributor

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.

0 Kudos
CCWeedcontrol
Regular Contributor

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).

0 Kudos
RandyBurton
MVP Regular Contributor

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.

0 Kudos
CCWeedcontrol
Regular Contributor

ya i had the wrong filed.

0 Kudos
MitchHolley1
MVP Regular Contributor

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.