nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Query date range

CCWeedcontrol

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

<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

Encontre mais publicações marcadas com

Accepted answers

RandyBurton

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

<SPAN class="keyword token">import</SPAN> arcpy
<SPAN class="keyword token">import</SPAN> datetime <SPAN class="keyword token">as</SPAN> DT

fc <SPAN class="operator token">=</SPAN> r<SPAN class="string token">"C:\Temp\BuildingPermit.gdb\ParcelData"</SPAN>
arcpy<SPAN class="punctuation token">.</SPAN>MakeFeatureLayer_management<SPAN class="punctuation token">(</SPAN>fc<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"PT_FC"</SPAN><SPAN class="punctuation token">)</SPAN>

from_date <SPAN class="operator token">=</SPAN> DT<SPAN class="punctuation token">.</SPAN>date<SPAN class="punctuation token">.</SPAN>today<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">-</SPAN> DT<SPAN class="punctuation token">.</SPAN>timedelta<SPAN class="punctuation token">(</SPAN>days<SPAN class="operator token">=</SPAN><SPAN class="number token">180</SPAN><SPAN class="punctuation token">)</SPAN>

SQL <SPAN class="operator token">=</SPAN> <SPAN class="string token">"created_date >= DATE '{}'"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>from_date<SPAN class="punctuation token">.</SPAN>strftime<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'%Y-%m-%d %H:%M:%S'</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN>

arcpy<SPAN class="punctuation token">.</SPAN>SelectLayerByAttribute_management<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"PT_FC"</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">"NEW_SELECTION"</SPAN><SPAN class="punctuation token">,</SPAN> SQL<SPAN class="punctuation token">)</SPAN>

arcpy<SPAN class="punctuation token">.</SPAN>FeatureClassToFeatureClass_conversion<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"PT_FC"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"C:\Temp"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"BP_TestA"</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

All comments

MitchHolley1

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.

RandyBurton

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.

RandyBurton

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

<SPAN class="keyword token">import</SPAN> arcpy
<SPAN class="keyword token">import</SPAN> datetime <SPAN class="keyword token">as</SPAN> DT

fc <SPAN class="operator token">=</SPAN> r<SPAN class="string token">"C:\Temp\BuildingPermit.gdb\ParcelData"</SPAN>
arcpy<SPAN class="punctuation token">.</SPAN>MakeFeatureLayer_management<SPAN class="punctuation token">(</SPAN>fc<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"PT_FC"</SPAN><SPAN class="punctuation token">)</SPAN>

from_date <SPAN class="operator token">=</SPAN> DT<SPAN class="punctuation token">.</SPAN>date<SPAN class="punctuation token">.</SPAN>today<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">-</SPAN> DT<SPAN class="punctuation token">.</SPAN>timedelta<SPAN class="punctuation token">(</SPAN>days<SPAN class="operator token">=</SPAN><SPAN class="number token">180</SPAN><SPAN class="punctuation token">)</SPAN>

SQL <SPAN class="operator token">=</SPAN> <SPAN class="string token">"created_date >= DATE '{}'"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>from_date<SPAN class="punctuation token">.</SPAN>strftime<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'%Y-%m-%d %H:%M:%S'</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN>

arcpy<SPAN class="punctuation token">.</SPAN>SelectLayerByAttribute_management<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"PT_FC"</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">"NEW_SELECTION"</SPAN><SPAN class="punctuation token">,</SPAN> SQL<SPAN class="punctuation token">)</SPAN>

arcpy<SPAN class="punctuation token">.</SPAN>FeatureClassToFeatureClass_conversion<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"PT_FC"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"C:\Temp"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"BP_TestA"</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

CCWeedcontrol

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'))
<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

RandyBurton

I've used something like:

<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> <SPAN class="keyword token">import</SPAN> datetime <SPAN class="keyword token">as</SPAN> DT
<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> from_date <SPAN class="operator token">=</SPAN> DT<SPAN class="punctuation token">.</SPAN>date<SPAN class="punctuation token">.</SPAN>today<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">-</SPAN> DT<SPAN class="punctuation token">.</SPAN>timedelta<SPAN class="punctuation token">(</SPAN>days<SPAN class="operator token">=</SPAN><SPAN class="number token">180</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> from_date
datetime<SPAN class="punctuation token">.</SPAN>date<SPAN class="punctuation token">(</SPAN><SPAN class="number token">2017</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">11</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">18</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> SQL <SPAN class="operator token">=</SPAN> <SPAN class="string token">"created_date >= '{}'"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>from_date<SPAN class="punctuation token">.</SPAN>strftime<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'%Y-%m-%d %H:%M:%S'</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> SQL
<SPAN class="string token">"created_date >= '2017-11-18 00:00:00'"</SPAN>

<SPAN class="comment token"># sometimes you need to indicate type is DATE</SPAN>
<SPAN class="comment token"># SQL = "created_date >= DATE '{}'".format(from_date.strftime('%Y-%m-%d %H:%M:%S'))</SPAN>
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

Participe da Conversa

Membros conectados podem postar, seguir atualizações e mais. Novo aqui? Registre uma conta gratuita.

Links Rápidos

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.