Dates and parentheses in sql query in arcpy

Question

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')

Answer 1

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:

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)