I have a created_date field in my (SDE) feature class. This selection by attributes statement works in ArcMap but I get an "Invalid Expression" error when trying to run it in arcpy. I have printed out the query and it looks to be probably formatted regarding quotations, but obviously I'm not typing it right and I wonder if I need to alter the "DatePart()" part of the formula.
countNumQuery = "DatePart(''"'yyyy'"'', created_date) = {0} AND MonitoringPointID = '{1}'".format(countYear, MPID)
Prints like this:
DatePart("yyyy", created_date) = 2020 AND MonitoringPointID = '{4FE9BDAB-9EBB-4947-93E5-244170084B54}'
I've also tried this with no luck
"EXTRACT(YEAR FROM created_date) = {0} AND MonitoringPointID = '{1}'".format(countYear, MPID)
countYear is a integer. MonitoringPointID is a GUID. This works in ArcMap:
Solved! Go to Solution.
Answer:
countNumQuery = "DatePart(year, created_date) = {0} AND MonitoringPointID = '{1}'".format(countYear, MPID)
The only documentation on DatePart() I could find from ESRI was this page about using it in Field Calculator; How To: Extract a portion of the Date field, thus the documentation is incomplete. I assume it wasn't working because it was an extraction format of VB, but there's no documentation that I could find that gave examples of python. I found my answer on another GeoNet thread that was tangentially related to my question.
Answer:
countNumQuery = "DatePart(year, created_date) = {0} AND MonitoringPointID = '{1}'".format(countYear, MPID)
The only documentation on DatePart() I could find from ESRI was this page about using it in Field Calculator; How To: Extract a portion of the Date field, thus the documentation is incomplete. I assume it wasn't working because it was an extraction format of VB, but there's no documentation that I could find that gave examples of python. I found my answer on another GeoNet thread that was tangentially related to my question.