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()
Solved! Go to Solution.
I believe you won't need the DATE in the expression since it's SQL Server database. Try:
expDate = "CreationDate < '{}'".format(str(month_ago))
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.
Using SQL.
How would I reformat it?
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
I believe you won't need the DATE in the expression since it's SQL Server database. Try:
expDate = "CreationDate < '{}'".format(str(month_ago))
Looks like in line 19 of code, the date isn't getting inserted in the string. Also "=" is being used instead of ">" or "<".