topic Re: field calcs with substring in Python Questions

field calcs with substring

JustinWolff — Tue, 13 Oct 2020 20:06:37 GMT

I have a text field with date values in YYYYMMDD format. I need to get them into the MM/DD/YYYY format in a text field (the target field for the MM/DD/YYYY values is text, not date). The source field containing the YYYYMMDD values is named 'LastUpdateDate' and the target for the MM/DD/YYYY values is named 'CAMAUpdate'. I'm attempting to use this:

SUBSTRING(!LastUpdateDate!,4,5)+'/'+SUBSTRING(!LastUpdateDate!,6,7)+'/'+SUBSTRING(!LastUpdateDate!,0,3)‍

In Pro 2.6.2 it fails with:

ERROR 000539: Traceback (most recent call last):
File "<expression>", line 1, in <module>
NameError: name 'SUBSTRING' is not defined

Source data is a polygon feature class in a file geodatabase.

Re: field calcs with substring

JoshuaBixby — Tue, 13 Oct 2020 21:56:23 GMT

The default expression type for Field Calculator in Pro is Python with the option to use Arcade. You appear to trying to use SQL, which isn't a supported expression type.

Trying this using the Python expression type:

"{}/{}/{}".format(!LastUpdateDate![4:6], !LastUpdateDate![6:], !LastUpdateDate![:4])‍

Re: field calcs with substring

JustinWolff — Wed, 14 Oct 2020 12:16:35 GMT

Thank you Joshua, that works. Now I need to work on error-handling, as my data has a lot of null and '0' values that I need to have come through as null.

Thanks again for the Python

Re: field calcs with substring

JoshuaBixby — Wed, 14 Oct 2020 15:00:52 GMT

The quickest way to get a result and move on is to make a selection against the table and filter out the records with NULL or 0.

You can also use a Python ternary operator to check whether the LastUpdateField has 8 characters, which would indicate a full date.

"{}/{}/{}".format(!LastUpdateDate![4:6], !LastUpdateDate![6:], !LastUpdateDate![:4]) if len(!LastUpdateDate!) == 8 else None‍

Re: field calcs with substring

JustinWolff — Fri, 10 Dec 2021 21:58:45 GMT

Josh - you're absolutely right, I was overthinking. Thanks. This is in a model anyway, so just selecting records that are not '0' or null, calculating and moving on is simple.

I was attempting to use code block but not having any luck (see below) but it's no big deal because I'll just use a selection:

def X(LastUpdateDate):
 if X is None:
 return '99/99/9999'
 elif X == '0':
 return '99/99/9999'
 else X == "{}/{}/{}".format(!LastUpdateDate![4:6], !LastUpdateDate![6:], !LastUpdateDate![:4]):
 return X‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍