1

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?

Improve this question

2 Answers 2

Reset to default
3

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

enter image description here

Improve this answer
11
  • 1
    Thank you for your response; I figured out the error but am now getting invalid syntax, also FYI I am using ArcGIS Pro. ```
    – Lena B.
    Dec 10, 2019 at 17:58
  • 1
    I have fixed it but I keep getting this error: arcpy.SelectLayerByAttribute_management('TestSample_10_Pickups', 'NEW_SELECTION', '"Trip_Start_Timestamp" = date '2019-6-1 00:00:00'') File "<string>", line 1 arcpy.SelectLayerByAttribute_management('TestSample_10_Pickups', 'NEW_SELECTION', '"Trip_Start_Timestamp" = date '2019-6-1 00:00:00'') ^ SyntaxError: invalid syntax
    – Lena B.
    Dec 10, 2019 at 18:01
  • 1
    I have tried multiple different styles of the date ranging from MM-DD-YYYY, YYYY-MM-DD, M-D-YYYY, YYYY-M-D, etc.
    – Lena B.
    Dec 10, 2019 at 18:02
  • @Lena B. Your example does not use two-digit month or day; date '2019-6-1 00:00:00' should be date '2019-06-01 00:00:00'
    – Vince
    Dec 10, 2019 at 18:06
  • I already tried that and get ``` arcpy.SelectLayerByAttribute_management('TestSample_10_Pickups', 'NEW_SELECTION', '"Trip_Start_Timestamp" = datetime '2019-06-01 00:00:00'') File "<string>", line 1 arcpy.SelectLayerByAttribute_management('TestSample_10_Pickups', 'NEW_SELECTION', '"Trip_Start_Timestamp" = datetime '2019-06-01 00:00:00'') ^ SyntaxError: invalid syntax```
    – Lena B.
    Dec 10, 2019 at 18:07
0

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.

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.