Query for more than 30 days

1542
5
Jump to solution
05-04-2017 10:14 AM
jaykapalczynski
Frequent Contributor

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() 
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍
0 Kudos
1 Solution

Accepted Solutions
JakeSkinner
Esri Esteemed Contributor

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

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

View solution in original post

5 Replies
JakeSkinner
Esri Esteemed Contributor

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.

0 Kudos
jaykapalczynski
Frequent Contributor

Using SQL.

How would I reformat it?

0 Kudos
RandyBurton
MVP Alum

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
0 Kudos
JakeSkinner
Esri Esteemed Contributor

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

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

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

0 Kudos