Convert String field to Date Field

[DonovanC]

Does anyone have any suggestions on how I can convert this field into a date/time.  I converted it from kml to shp file and all the information was in one field. I tried using the Convert Time Field tool but it failed.

@XanderBakker 

@HusseinNasser2 

[Robert_LeClair]

Yes, what you could do is add a new attribute field that is a DATE field type.  Then in the open Attribute Table, right-click on the new DATE field and select Calculate Field.  In the Calculate Field GP tool, calculate the DATE field = kml_desc field.  It "should" convert the data in the kml_desc field to date/time in the DATE field.  Hope this helps!

[DonovanC]

Hi Robert,

I tried this solution but I get an error when trying to calculate the field. I think it is failing because some records contain 2 dates & timestamps along with numbers at the end of it.

Here is the calculation:

Here is the error I am getting:

[JoshuaBixby · Accepted solution]

Although the date format is standard, it is wrapped in additional text that is causing troubles for the tool.  Additionally, some rows have multiple dates, which also will cause issues for the tool.

You will need to do some text parsing to extract just a single, valid date format before trying to convert to a date.

[XanderBakker]

Hi @DonovanC ,

In addition to what Joshua mentioned, it seems you have different date notations MM/DD/YYYY and DD-MM-YYYY and there is more than a single date in the text field. 

To provide an example to extract the first data based on what I have seen from the screenshot, here is an example with Arcade (in Python this could  be done in a lot less lines):

Function Convert2DateTime(txt) {
    var dttxt = Mid(txt, 1, 22);
    var y = Number(Mid(dttxt, 6, 4));
    var mi = Number(Mid(dttxt, 14, 2));
    var s = Number(Mid(dttxt, 17, 2));
    if (Find("-", dttxt) == -1) {
        // MM/DD/YYYY HH:mm:SS AP
        // 0123456789012345678901
        // 05/09/2020 10:46:41 AM
        var mo = Number(Mid(dttxt, 0, 2)) -1;
        var d = Number(Mid(dttxt, 3, 2));
        var ampm = Right(dttxt, 2);
        if (ampm == "AM") {
            var h = Number(Mid(dttxt, 11, 2));
        } else {
            var h = Number(Mid(dttxt, 11, 2)) + 12;
        }
    } else {
        // DD-MM-YYYY HH:mm:SS
        // 0123456789012345678
        // 09-05-2020 10:46:41
        var mo = Number(Mid(dttxt, 3, 2)) -1;
        var d = Number(Mid(dttxt, 0, 2));
        var h = Number(Mid(dttxt, 11, 2));
    }
    // Date(year, month, day, hours, minutes, seconds)
    return Date(y, mo, d, h, mi, s);
}

var dts = ['<09-05-2020 10:46:41> 0-1 <09-05-2020 10:46:46>', 
           '<09/05/2020 10:55:17 AM> 1'];

for (var i in dts) {
    var txt = dts[i];
    Console("txt: " + txt);
    var dt = Convert2DateTime(txt);
    Console("dt: " + dt);
}

return "OK";

In this example, two different formats as send to the function to validate the result. It will print the following to the console:

txt: <09-05-2020 10:46:41> 0-1 <09-05-2020 10:46:46>
dt: 2020-05-09T10:46:41-05:00

txt: <09/05/2020 10:55:17 AM> 1
dt: 2020-09-05T10:55:17-05:00

[DonovanC]

Hi @XanderBakker ,

When I ran that code in the Field Calculator and outputted "dt" as the return this was the end result:

It did not output the same time but a random timestamp, any idea why that is happening.

I am novice user with Python and trying to learn more, I am curious to see how the code is different from Arcade.

Thanks everybody for all their help!

[XanderBakker]

Hi @DonovanC ,

I think I see what is going on. The expression is taking 4 characters for the year, when in this case there are only 2. This should be corrected in the code. The other thing that will probably influence the result is the fact that the time is returned in UTC. 

[XanderBakker]

Hi @DonovanC ,

Could you try with this function:

Function Convert2DateTime(txt) {
    var dttxt = Mid(txt, 1, 20);
    Console("dttxt: " + dttxt);
    if (Find("-", dttxt) == -1) {
        // MM/DD/YYYY HH:mm:SS AP
        // 0123456789012345678901
        // 05/09/20 10:46:41 AM
        var y = Number(Mid(dttxt, 6, 2)) + 2000;
        var mo = Number(Mid(dttxt, 0, 2)) - 1;
        var d = Number(Mid(dttxt, 3, 2));
        var mi = Number(Mid(dttxt, 12, 2));
        var s = Number(Mid(dttxt, 15, 2));
        var ampm = Right(dttxt, 2);
        if (ampm == "AM") {
            var h = Number(Mid(dttxt, 9, 2));
        } else {
            var h = Number(Mid(dttxt, 9, 2)) + 12;
        }
    } else {
        // DD-MM-YYYY HH:mm:SS
        // 0123456789012345678
        // 09-05-2020 10:46:41
        dttxt = Left(dttxt, 19);
        var y = Number(Mid(dttxt, 6, 4));
        var mo = Number(Mid(dttxt, 3, 2)) -1;
        var d = Number(Mid(dttxt, 0, 2));
        var h = Number(Mid(dttxt, 11, 2));
        var mi = Number(Mid(dttxt, 14, 2));
        var s = Number(Mid(dttxt, 17, 2));
    }
    // Date(year, month, day, hours, minutes, seconds)
    return Date(y, mo, d, h, mi, s);
}

[DonovanC]

Hi @XanderBakker 

The expression checks out but it returns NULL

[XanderBakker]

Hi @DonovanC ,

Do you have these two additional lines at the end of the expression (to read the attribute and execute the function with the kml description?

var txt = $feature["kml_des"];
return Convert2DateTime(txt);