field calcs with substring

1127
4
Jump to solution
10-13-2020 01:06 PM
JustinWolff
Occasional Contributor

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.

Tags (2)
0 Kudos
1 Solution

Accepted Solutions
JoshuaBixby
MVP Esteemed Contributor

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

View solution in original post

4 Replies
JoshuaBixby
MVP Esteemed Contributor

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])
JustinWolff
Occasional Contributor

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

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

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
JustinWolff
Occasional Contributor

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‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍
0 Kudos