Selecting features by year in datetime field

Question

I have a shapefile with a datetime field. I want to split the features by year and save into a geodatabase. My approach has been to create a list of years, and from there iterate over the list and perform a select by. However I've struggled to make a selection - see below -

import os
import arcpy
from datetime import datetime

SHP = "D:\Data\Footprints\Footprints_test.shp"

#create gdb
arcpy.CreateFileGDB_management("D:\Data\Footprints", "Footprints_test.gdb")
GDB = "D:\Data\Footprints\Footprints_test.gdb"

#calculate date field
arcpy.management.ConvertTimeField(SHP, "raw_date", "yyyy-MM-ddTHHmmss.s;3081", "cap_date", "DATE", '')

#years
year_list = [(set(datetime.strftime(row[0], "%Y") for row in arcpy.da.SearchCursor(SHP, "cap_date")))]

#save to gdb
for x in year_list:
    arcpy.SelectLayerByAttribute_management(SHP, "NEW_SELECTION", cap_date_q.date.year = x)
    arcpy.FeatureClassToFeatureClass_conversion(SHP, GDB, "Footprints_"+str(x))
    arcpy.SelectLayerByAttribute_management(SHP, "CLEAR_SELECTION")

Obviously the

cap_date_q.date.year = x

syntax is wrong. However, I've really struggled through the documentation.

How do I properly interact with the date field?

Answer 1

arcpy.SelectLayerByAttribute_management expects you to specify a field based SQL as the where_clause. Your cap_date_q.date.year looks like a class property in python not a proper clause. Since you are after only the years, you can use EXTRACT operator, for example:

SelectLayerByAttribute_management(SHP, "NEW_SELECTION", 'EXTRACT(YEAR FROM "cap_date" ) = {}'.format(x))

presuming your list year_list is populated as expected.

For references to the date extraction from a date field is in https://support.esri.com/en/technical-article/000011531