XYTableToPoint from CSV failing for Date fields

[RAnkeny]

I'm on ArcGIS Pro 3.1 and have created a stand alone python script to import data from an outside SQL table with Lat/Long fields into a file GDB feature class using the XYTableToPoint method and an extracted CSV. The issue is it fails converting all of the data fields as dates. Unfortunately the table I am extracting from has multiple date fields as different data types. The first one is a smalldatetime and the rest are datetime. (it's not mine, I cant change it and I didn't create it)

When extracted to CSV and then converted to a table in the FGDB, it successfully converts the smalldatetime to a date field, but the other ones it imports as TEXT fields.  I cannot see a difference between any of these fields when looking at he raw CSV data.

I have added a SQL CAST into the extraction script to cast all of the datetime fields into smalldatetime and that works for the very first one, but it ignores the rest of them (no matter what field I put first).

Am I running into a bug here? 

[TonyAlmeida]

Try using the the datetime.datetime module in Python to handle the conversion of the datetime fields to the appropriate format.

# Iterate through date fields and convert them to proper format
for field in arcpy.ListFields("TempTable"):
    if field.type == "Date":
        expression = "datetime.datetime.strptime(!{0}!, '%Y-%m-%d %H:%M:%S')".format(field.name)
        arcpy.CalculateField_management("TempTable", field.name, expression, "PYTHON3")

[RAnkeny · Accepted solution]

Thank you Tony, while your code was not exactly what fixed the issue it did give me the push to process the table in mem instead of using a CSV file as an interim table. Thanks for the breadcrumb.

psudo-code:

 arcpy.management.MakeTableView(inputTable, 'TempTable', whereClause)

 arcpy.env.outputCoordinateSystem = arcpy.SpatialReference(2286)

 arcpy.env.workspace = outputWorkspace

 arcpy.management.XYTableToPoint("TempTable",OutputFeatureClass , "LON", "LAT")