Having trouble with a function that converts dates between date and text type

jacob_ekn

I made a script tool that takes in table data from another team, performs an XY table to point, does some analysis, then outputs a final feature class with fields all formatted and in the right order. This was all working fine until it came time for updates and the XY table to point tool started changing some of the date fields in the Excel table to a different type than the original table that we have hosted on AGOL (we use the Update Data function in AGOL with the newly created/updated feature class from running the tool).

To try to fix this, I created a function that creates a new field for each date, checks the type, and calculates the new field to be the correct type based on the original table (the hosted feature layer).

Here's the function:

def date_verify(in_table, in_field, out_field, ver):
    date_func = '''
    function GetDate(date_text) {
        var date_list = Split(date_text, '/');
        var y = Number(date_list[2]);
        var m = Number(date_list[0]) - 1;
        var d = Number(date_list[1]);

        return Date(y, m, d);
    }

    function GetString(date) {
        return Text(date, "MM/DD/YYYY");
    }
    '''
    
    field_calc = f'''
    {date_func}

    var val = $feature.{in_field};

    if (IsEmpty(val)) return null;
    else return Get{ver}(val);
    '''

    temp_type = [f.type for f in arcpy.ListFields(in_table) if f.name == in_field][0]
    curr_type = "Date" if temp_type == "Date" or temp_type == "DateOnly" else "String"
    ver_type = "TEXT" if ver == "String" else "DATE"

    if (ver != curr_type):
        arcpy.management.AddField(in_table=in_table, field_name=out_field, field_type=ver_type)
        arcpy.management.CalculateField(in_table=in_table, field=out_field, expression=field_calc, expression_type="ARCADE")
    else:
        arcpy.management.AddField(in_table=in_table, field_name=out_field, field_type=ver_type)
        arcpy.management.CalculateField(in_table=in_table, field=out_field, expression=f"return $feature.{in_field}", expression_type="ARCADE")

    return

I keep getting an Index Out of Bounds error specifically from the GetDate function in the Arcade expression, and based on the line in my script where it keeps happening, it seems that the conditional (on line 30 here) is being met when it shouldn't. Is there something I'm missing? Or is it possible to get XY Table to Point to get all the field types correct so I don't have to bother with this in the first place?

Tonyalmeida

This will help you see if the dates are being parsed and formatted correctly.

function GetDate(date_text) {
    if (IsEmpty(date_text) || !Contains(date_text, '/')) return null;
    var date_list = Split(date_text, '/');
    if (Count(date_list) < 3) return null;
    var y = Number(date_list[2]);
    var m = Number(date_list[0]) - 1;
    var d = Number(date_list[1]);
    
    // Debug output
    return Text(Date(y, m, d), "MM/DD/YYYY");
}