topic Query for more than 30 days in Python Questions

Query for more than 30 days

jaykapalczynski — Sat, 11 Dec 2021 17:22:22 GMT

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\\xxxx\\AppData\\Roaming\ESRI\\Desktop10.4\\ArcCatalog\\xxxx_Admin.sde"
fc = "xxxxx"

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

Re: Query for more than 30 days

JakeSkinner — Thu, 04 May 2017 17:47:44 GMT

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.

Re: Query for more than 30 days

jaykapalczynski — Thu, 04 May 2017 18:06:48 GMT

Using SQL.

How would I reformat it?

Re: Query for more than 30 days

RandyBurton — Sat, 11 Dec 2021 17:22:25 GMT

My thoughts:

import datetime as DT
today = DT.date.today()
month_ago = today - DT.timedelta(days=30)

expDate = "CreationDate < DATE '{}'".format(month_ago)
print expDate
‍‍‍‍‍‍

Re: Query for more than 30 days

JakeSkinner — Thu, 04 May 2017 18:28:55 GMT

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

expDate = "CreationDate < '{}'".format(str(month_ago))

Re: Query for more than 30 days

RandyBurton — Thu, 04 May 2017 18:34:55 GMT

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