Select by recent date - specific date field

Question

There is nothing more frustrating in working with a GDB FC/Table then dealing with a date field.

I am simply trying to run a Select Layer by Attribute tool via Python to select the most recent date in a file GDB feature class in a date field type. All my research have failed in being successful.

I have a Make Feature Layer and Select Layer by Attribute tool.

The recent expression:

arcpy.management.SelectLayerByAttribute(SURVEY123_EWO_REGULAR_HOURS_FL, "NEW_SELECTION", "date_ewo = (SELECT MAX(date_ewo) FROM SURVEY123_EWO_REGULAR_HOURS)")

Presently, I have (3) entries in the Feature Layer and still the selection exports all (3) entries versus just (1). In the future, this FC is going to grow and I need to be able to select the most recent entry each time I run the tool.

Appreciate any help

Robert LeClair · Answer

I don't think using the DATE_ONLY field vs the "older" DATE_TIME field would matter.  The DATE_ONLY field would return a Month-Date-Year field and not have the time obviously.

ModernGIS · Answer

@Robert_LeClair @DanPatterson

Thank you for the information. I've tried the script you sent, and also found another script:

with arcpy.da.SearchCursor(feature_class, [date_field]) as cursor:
    max_date = None
    for row in cursor:
        date_value = row[0]
        if max_date is None or date_value > max_date:
            max_date = date_value

print("Maximum date: ", max_date)

arcpy.SelectLayerByAttribute_management(feature_class, "NEW_SELECTION", date_value)

arcpy.conversion.ExportFeatures(feature_class, layername_export)
print("EXPORT")

Both of the scripts exports all of the rows from the feature class to a new feature class. The goal is to only select and export the newest date regardless if there is 1 feature or 200 features in the feature class. As time progresses, this feature class is going to grow overtime and the next script(s) will rely on having (1) record and it needs to be the most recent record.

Question, does that need to be a DATE_ONLY field type or is it okay to be a DATE-TIME  field type? Not sure if this makes a difference.

Robert LeClair · Answer

Caveat - I am not a Python person but I found a Python script written via this page that seems to accomplish the task at hand: listdates = [] cursor = arcpy.da.SearchCursor(layername, "Date_Mod") for row in cursor: listdates.append(row) maxdate = max(listdates) maxdatestr = str(maxdate) datestr = maxdatestr[19:31] arcpy.SelectLayerByAttribute_management(layername, "NEW_SELECTION", "Date_Mod = date '" + datestr + " 00:00:00'")