ArcPy Select Layer by Attribute for Date and Time

Question

I have a list of trips with a field, "Trip Start Timestamp" thats type is date where it appears on the attribute table as M/D/YYYY HH:MM:SS AM/PM I need group all trips with the same trip start timestamp, for example 06/01/2019 12:00:00 AM. I am running the arcpy select layer by attributes as shown below:

arcpy.SelectLayerByAttribute_management('TestSample_10_Pickups', 'NEW_SELECTION', 'Trip_Start_Timestamp = "2019-06-01 12:00:00 AM"')
Traceback (most recent call last):
  File "<string>", line 1, in <module>
  File "c:\program files\arcgis\pro\Resources\arcpy\arcpy\management.py", line 8123, in SelectLayerByAttribute
    raise e
  File "c:\program files\arcgis\pro\Resources\arcpy\arcpy\management.py", line 8120, in SelectLayerByAttribute
    retval = convertArcObjectToPythonObject(gp.SelectLayerByAttribute_management(*gp_fixargs((in_layer_or_view, selection_type, where_clause, invert_where_clause), True)))
  File "c:\program files\arcgis\pro\Resources\arcpy\arcpy\geoprocessing\_base.py", line 506, in <lambda>
    return lambda *args: val(*gp_fixargs(args, True))
arcgisscripting.ExecuteError: ERROR 000358: Invalid expression
Failed to execute (SelectLayerByAttribute).

I am completely lost as to what this error is and if it has to do with the formatting of the date and time. Does anyone know how to select layer attributes by date AND time?

Answer 1

You need date at the start of each date and single quotes for its value. You may also need to use 00:00:00 for midnight. Try selecting by attribute in ArcMap first and copying its format.

"Trip_Start_Timestamp = date '2019-06-01 00:00:00'"

Answer 2

Given last comment I think this '"Trip_Start_Timestamp" = datetime '2019-06-01 00:00:00'') has incorrect quotes.

Try changing to

"Trip_Start_Timestamp=timestamp'2019-12-11 00:00:00'"

or

"Trip_Start_Timestamp=date'2019-12-11 00:00:00'"

I don't diff between using date v timestamp, but in Pro the Select By Attributes GP tool used timestamp in the SQL.

As an aside ... date\time can be tricky in SQL, specifying an exact time might be fine but if you start trying to do date\time ranges you have to get the times correct otherwise you miss or get extra records.