SQL-query in arcpy - SELECT WHERE feature between two values

Question

I need to select features between two values in python with arcpy. However, whenever I try to run the code below, it returns with error message "An invalid SQL statement was used".

whereclause = """"X" BETWEEN {0} AND {1}""".format(
 min(X)[0]-1000,
 max(X)[0]+1000)
ap.FeatureClassToFeatureClass_conversion(SamledeMaalinger,ap.env.workspace,"SamledeMaalingerSort", whereclause)

The whereclause returns "X" BETWEEN 555157.0723 AND 557157.0723 so it's not the string that's the issue.

The SQL-query works if I use < and> instead, but the BETWEEN for some reason doesn't.

So what's the issue? Do the SQL-queries in arcpy have limitations?

• Alex Tereshenkov: where do you store the gdb?
• Stephen Lead: According to About building an SQL expression you should be able to use BETWEEN: You can use greater than (>), less than (<), greater than or equal (>=), less than or equal (<=), and BETWEEN operators
• matt wilkie: perhaps failing because the numbers are being compared as strings instead when BETWEEN used, while < forces numeric(?)

Answer 1

As @AlexTereshnekov commented, the problem may be caused by your spatial data storage choice.

I just used the expression below successfully in a file geodatabase feature class:

OID BETWEEN 1 AND 100

but in a shapefile this expression reported that it was invalid SQL:

"FID" BETWEEN 1 AND 100

My testing used ArcGIS 10.2.2 for Desktop