I am trying to select records within a specified date range from a file geodatabase feature class using an sql query but am having a hard time getting the syntax correct. I have tried numerous ways but at this point am getting no records returned. The last couple of versions of the query I have tried are as follows:

whereClause = """'{0}' > '{1}' AND '{0}' < '{2}'""".format(arcpy.AddFieldDelimiters(fcMaster, "date"), '2016-10-02 02:30:00', '2016-10-05 02:30:00')

whereClause = """'{0}' BETWEEN '{1}' AND '{2}'""".format(arcpy.AddFieldDelimiters(fcMaster, "date"), 'date 2016-10-02 02:30:00', 'date 2016-10-05 00:02:30')
  • 1
    Since you are using AddFieldDelimiters, don't wrap {0} in quotes -- the function handles that for you if your data source changes. – Paul Oct 10 '16 at 18:54
  • Welcome to GIS SE! What does it look like when you use the Select by Attributes window in ArcMap? Your whereclause here should look the same as in that window. Try using print whereclause to see what python is using. – Midavalo Oct 10 '16 at 18:56

Almost there.

As per @Paul's comment, you don't want your {0} in quotes as the AddFieldDelimiters handles that for you if required.

You also want to move the word date outside of your quotes in '{1}' so it's date '{1}', and then in the string format remove "date" from the values passed, so 'date 2016-10-02 02:30:00' should become '2016-10-02 02:30:00'

Try something like this:

whereClause = """ {0} BETWEEN date '{1}' AND date '{2}' """.format(arcpy.AddFieldDelimiters(fcMaster, "date"), '2016-10-02 02:30:00', '2016-10-05 00:02:30')

When writing selection queries like this I always construct it first in the Select by Attributes window within ArcMap to see what it should look like, and then use a print statement on my where clause in arcpy to make sure that my query looks the same as it does in the Select by Attributes window.

For example this is what I just tested with to get the syntax for this answer:

enter image description here

Also note: your second date/time value uses 00:02:30 (2 and half minutes past midnight) and looks like it should actually be 02:30:00 (2:30 AM)

| improve this answer | |

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.