I am working on a Tool which selects records depending on the input value. Works fine, till I want to select date-records. I used

def buildWhereClause(self, table, field, value):

    # Add DBMS-specific field delimiters
    fieldDelimited = ap.AddFieldDelimiters(table, field)

    # Determine field type
    fieldType = ap.ListFields(table, field)[0].type

    # Add single-quotes for string field values
    if str(fieldType) == 'String':
        value = "'%s'"% value
    elif str(fieldType) == 'Double':
        value = float(value)
    elif str(fieldType) == 'Date':
        value = datetime.strptime(value,'%d.%m.%Y')

That's my idea of the quote which I added to accept date-format in the inputValue. But when I am typing in for example 07.01.2019 (a date which exists in my table), I get this error: ExecuteError: ERROR 000358: Invalid expression Failed to execute (SelectLayerByAttribute).

    # Format WHERE clause
    whereClause = "%s = %s" % (fieldDelimited, value)
    return whereClause

def execute(self, parameters, messages):
    """The source code of the tool."""

    inputFeature = parameters[0].valueAsText
    inputFieldname = parameters[1].valueAsText
    inputValue = parameters[2].valueAsText
    outputFeature = parameters[3].valueAsText

    whereClause = self.buildWhereClause(inputFeature, inputFieldname, inputValue)

    ap.SelectLayerByAttribute_management(inputFeature,"NEW_SELECTION", whereClause)


I am wondering if my understanding of the function of this method is basically wrong, or if it's just an issue with the syntax...

  • Add the fc to ArcMap, open attribute table and select by attributes. Double-click date field, click equal to, and press Get Unique Values. ArcMap will show you the correct date syntax. Probably date '2001-01-01' but it will depend on input source (fgdb, enterprise db, etc.) – BERA Jan 16 '19 at 12:50
  • I think the date format is date 'YYYY-mm-dd HH:MM:SS' Shapefile is not capable of retaining the HMS components. – Vince Jan 16 '19 at 13:05
  • @BERA Thank you. If I copy one of the values i get from the "Get Unique Values"-Button and try to use it as the input value, I still get an error. Is it possible to add the values of "Get Unique Values" as a dropdown list to where the User has to type in the value? – Andreas Jan 16 '19 at 13:13
  • @Vince Yes it has excatly that format. – Andreas Jan 16 '19 at 13:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.