topic Re: Query date range in Python Questions

Query date range

CCWeedcontrol — Sun, 12 Dec 2021 04:57:43 GMT

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

‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Query date range

RandyBurton — Sun, 12 Dec 2021 04:57:45 GMT

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'))
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Query date range

CCWeedcontrol — Sun, 12 Dec 2021 04:57:48 GMT

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'))
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Query date range

RandyBurton — Sun, 12 Dec 2021 04:57:51 GMT

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

Re: Query date range

RandyBurton — Thu, 17 May 2018 22:56:26 GMT

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.

Re: Query date range

CCWeedcontrol — Sun, 12 Dec 2021 04:57:53 GMT

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

Re: Query date range

MitchHolley1 — Tue, 22 May 2018 15:06:04 GMT

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.

Re: Query date range

RandyBurton — Tue, 22 May 2018 16:32:03 GMT

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.

Re: Query date range

CCWeedcontrol — Tue, 22 May 2018 17:44:28 GMT

ya i had the wrong filed.