topic Re: Convert String field to Date Field in ArcGIS Pro Questions

Convert String field to Date Field

DonovanC — Tue, 12 Jan 2021 22:00:20 GMT

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

Re: Convert String field to Date Field

Robert_LeClair — Tue, 12 Jan 2021 23:11:12 GMT

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!

Re: Convert String field to Date Field

DonovanC — Wed, 13 Jan 2021 13:59:39 GMT

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:

Re: Convert String field to Date Field

JoshuaBixby — Wed, 13 Jan 2021 17:24:51 GMT

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.

Re: Convert String field to Date Field

XanderBakker — Wed, 13 Jan 2021 19:06:24 GMT

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

Re: Convert String field to Date Field

DonovanC — Wed, 13 Jan 2021 21:08:43 GMT

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!

Re: Convert String field to Date Field

XanderBakker — Wed, 13 Jan 2021 22:24:30 GMT

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.

Re: Convert String field to Date Field

XanderBakker — Wed, 13 Jan 2021 22:36:16 GMT

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); }

Re: Convert String field to Date Field

DonovanC — Wed, 13 Jan 2021 22:45:53 GMT

Hi @XanderBakker

The expression checks out but it returns NULL

Re: Convert String field to Date Field

XanderBakker — Wed, 13 Jan 2021 23:03:39 GMT

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);

Re: Convert String field to Date Field

DonovanC — Thu, 14 Jan 2021 14:32:57 GMT

Hi @XanderBakker

I added in the two additional lines of the expressions but it does not pull the timestamp.

Re: Convert String field to Date Field

XanderBakker — Thu, 14 Jan 2021 15:25:11 GMT

Hi @DonovanC ,

As far as I understand, the way a date time is shown in ArcGIS Pro depends on the settings of your system: https://pro.arcgis.com/en/pro-app/latest/help/data/tables/date-fields.htm

You can validate this using the pop-up and define an expression to return the date and time using the format that you want. An example:

return Text($feature["Name of your date field"], "Y-MM-DD HH:mm:ss");

Re: Convert String field to Date Field

XanderBakker — Thu, 14 Jan 2021 16:02:08 GMT

Hi @DonovanC ,

Earlier on you also mentioned using Python for the field calculation. This should return the same result.

def ParseDateTime(tst): import datetime dt_format1 = "%d-%m-%Y %H:%M:%S" dt_format2 = "%m/%d/%y %I:%M:%S %p" if "-" in tst: txt = tst[1:20] dt = datetime.datetime.strptime(txt, dt_format1) return dt else: txt = tst[1:21] dt = datetime.datetime.strptime(txt, dt_format2) return dt

ParseDateTime(!kml_des!)

Re: Convert String field to Date Field

DonovanC — Thu, 14 Jan 2021 16:07:03 GMT

Thanks for all your help!!