Trying to query records for older than 30 days from present.... Blowing up here...dont have to proper syntax,,,,I am trying to use two variables.. expDateMonthAgo = "CreationDate = [month_ago]" Thoughts? #Variables============================================== arcpy . env . workspace = "C:\\Users\\xyz\\AppData\\Roaming\ESRI\\Desktop10.4\\ArcCatalog\\xyz_Admin.sde" fc = "xyz" var_additionalcomments = 'additionalcomments' var_CreationDate , var_Creator = 'CreationDate' , 'Creator' headers = [ 'comments' , 'Creation Date' , 'Creator' ] QueryFields = [ var_additionalcomments , var_CreationDate , var_Creator ] import datetime as DT today = DT . date . today ( ) month_ago = today - DT . timedelta ( days = 30 ) print month_ago expDateMonthAgo = "CreationDate = [month_ago]" expDate = "({})" . format ( expDateMonthAgo ) expressionDate = expDate outFileRegionDate = open ( r "E:\ArcGISProjects\\PythonScripts\PythonSync\PythonScripts\z_outFileRegionDate.csv" , "w" ) mylist = [ ] with arcpy . da . SearchCursor ( fc , QueryFields , where_clause = expressionDate ) as cursorDate : outFileRegionDate . write ( ',' . join ( headers ) + '\n' ) flagDate = False for row in cursorDate : flagDate = True zvalDate = str ( '{0},{1},{2}' . format ( row [ 0 ] , row [ 1 ] , row [ 2 ] ) ) outFileRegionDate . write ( zvalDate + "%s\n" ) mylist . append ( zvalDate ) if not flagDate : print "No rows found" outFileRegionDate . close ( )

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 for more than 30 days

jaykapalczynski

Trying to query records for older than 30 days from present....

Blowing up here...dont have to proper syntax,,,,I am trying to use two variables..

expDateMonthAgo = "CreationDate = [month_ago]"

Thoughts?

<SPAN class="comment token">#Variables==============================================</SPAN>

arcpy<SPAN class="punctuation token">.</SPAN>env<SPAN class="punctuation token">.</SPAN>workspace <SPAN class="operator token">=</SPAN> <SPAN class="string token">"C:\\Users\\xyz\\AppData\\Roaming\ESRI\\Desktop10.4\\ArcCatalog\\xyz_Admin.sde"</SPAN>
fc <SPAN class="operator token">=</SPAN> <SPAN class="string token">"xyz"</SPAN>

var_additionalcomments <SPAN class="operator token">=</SPAN> <SPAN class="string token">'additionalcomments'</SPAN>
var_CreationDate<SPAN class="punctuation token">,</SPAN>var_Creator <SPAN class="operator token">=</SPAN> <SPAN class="string token">'CreationDate'</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">'Creator'</SPAN>

headers <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">'comments'</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">'Creation Date'</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">'Creator'</SPAN><SPAN class="punctuation token">]</SPAN>

QueryFields <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN>var_additionalcomments<SPAN class="punctuation token">,</SPAN>var_CreationDate<SPAN class="punctuation token">,</SPAN>var_Creator<SPAN class="punctuation token">]</SPAN>


<SPAN class="keyword token">import</SPAN> datetime <SPAN class="keyword token">as</SPAN> DT
today <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>
month_ago <SPAN class="operator token">=</SPAN> today <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">30</SPAN><SPAN class="punctuation token">)</SPAN>

<SPAN class="keyword token">print</SPAN> month_ago
expDateMonthAgo <SPAN class="operator token">=</SPAN> <SPAN class="string token">"CreationDate = [month_ago]"</SPAN>
expDate <SPAN class="operator token">=</SPAN> <SPAN class="string token">"({})"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>expDateMonthAgo<SPAN class="punctuation token">)</SPAN>
expressionDate <SPAN class="operator token">=</SPAN> expDate

outFileRegionDate <SPAN class="operator token">=</SPAN> open<SPAN class="punctuation token">(</SPAN>r<SPAN class="string token">"E:\ArcGISProjects\\PythonScripts\PythonSync\PythonScripts\z_outFileRegionDate.csv"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"w"</SPAN><SPAN class="punctuation token">)</SPAN>&nbsp; 

mylist<SPAN class="operator token">=</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="punctuation token">]</SPAN>
<SPAN class="keyword token">with</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>SearchCursor<SPAN class="punctuation token">(</SPAN>fc<SPAN class="punctuation token">,</SPAN> QueryFields<SPAN class="punctuation token">,</SPAN> where_clause<SPAN class="operator token">=</SPAN>expressionDate<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> cursorDate<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp; outFileRegionDate<SPAN class="punctuation token">.</SPAN>write<SPAN class="punctuation token">(</SPAN><SPAN class="string token">','</SPAN><SPAN class="punctuation token">.</SPAN>join<SPAN class="punctuation token">(</SPAN>headers<SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">+</SPAN> <SPAN class="string token">'\n'</SPAN><SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp; flagDate <SPAN class="operator token">=</SPAN> <SPAN class="token boolean">False</SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> cursorDate<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; flagDate <SPAN class="operator token">=</SPAN> <SPAN class="token boolean">True</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; zvalDate <SPAN class="operator token">=</SPAN> str<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'{0},{1},{2}'</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">,</SPAN>row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">,</SPAN>row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">2</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; outFileRegionDate<SPAN class="punctuation token">.</SPAN>write<SPAN class="punctuation token">(</SPAN>zvalDate <SPAN class="operator token">+</SPAN> <SPAN class="string token">"%s\n"</SPAN><SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; mylist<SPAN class="punctuation token">.</SPAN>append<SPAN class="punctuation token">(</SPAN>zvalDate<SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">if</SPAN> <SPAN class="operator token">not</SPAN> flagDate<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">print</SPAN> <SPAN class="string token">"No rows found"</SPAN>
outFileRegionDate<SPAN class="punctuation token">.</SPAN>close<SPAN class="punctuation token">(</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>‍</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><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

Finde mehr Beiträge mit dem Tag

Accepted answers

JakeSkinner

I believe you won't need the DATE in the expression since it's SQL Server database. Try:

expDate <SPAN class="">=</SPAN> <SPAN class="">"CreationDate < '{}'"</SPAN><SPAN class="">.</SPAN>format<SPAN class="">(str(</SPAN>month_ago<SPAN class="">))</SPAN>

All comments

RandyBurton

Looks like in line 19 of code, the date isn't getting inserted in the string. Also "=" is being used instead of ">" or "<".

JakeSkinner

I believe you won't need the DATE in the expression since it's SQL Server database. Try:

expDate <SPAN class="">=</SPAN> <SPAN class="">"CreationDate < '{}'"</SPAN><SPAN class="">.</SPAN>format<SPAN class="">(str(</SPAN>month_ago<SPAN class="">))</SPAN>

RandyBurton

My thoughts:

<SPAN class="keyword token">import</SPAN> datetime <SPAN class="keyword token">as</SPAN> DT
today <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>
month_ago <SPAN class="operator token">=</SPAN> today <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">30</SPAN><SPAN class="punctuation token">)</SPAN>

expDate <SPAN class="operator token">=</SPAN> <SPAN class="string token">"CreationDate < DATE '{}'"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>month_ago<SPAN class="punctuation token">)</SPAN>
<SPAN class="keyword token">print</SPAN> expDate
<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

jaykapalczynski

Using SQL.

How would I reformat it?

JakeSkinner

Hi Jay,

What Enterprise Geodatabase are you using? (i.e. Oracle, SQL Server) You will most likely have to reformat the 'month_ago' variable. It is currently read as datetime.data(2017, 4, 4). SQL Server will query dates using:

'2017-04-04'

Oracle will use:

timestamp '2017-04-04'

I always do a select by attribute or create a definition query when it comes to dates to make sure I have the syntax correct.

Nimm an der Unterhaltung teil

Angemeldete Mitglieder können Beiträge verfassen, Updates folgen und mehr. Neu hier? Registriere ein kostenloses Konto.

Schnellzugriffe

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