Extract a portion of a date field: Field calculator

ToddFagin

This is a statement as much as a question because I solved my issue. However, others may encounter something similar, so I will go ahead and post this.

I was looking for a DatePart equivalent for field calculator in ArcGIS Pro (e.g. in ArcMap could use something like DatePart("YYYY", [datefield])). 

I tried using SQL: EXTRACT(YEAR FROM "datefield"), which works for a query but apparently not for a field calculation. Anyone know why this may be?

Finally, I was able to use Python 3: !datefield!.strftime('%Y') (I discovered this is also case sensitive ('%y') only returns year without century.

The documentation on this seems to be poor (or I am not very good at finding it). Hopefully, this will help others who have a similar problem. This resource also proved to be very useful.

JoshuaBixby

Todd, I am glad you were able to sort out your issue, and it is always good to post the information back to share with others that may run into a similar issue.

Although SQL is supported as an expression type with Field Calculator in ArcGIS Pro, it is limited support.  From Calculate Field—Data Management toolbox | ArcGIS Desktop:

To learn more about SQL expressions, see Calculate field values.

SQL expressions were implemented to better support calculations against feature services, particularly with performance. Instead of performing calculations one feature or row at a time, a single request is set to the feature service.

Only feature services and enterprise geodatabases support SQL expressions. For other formats, use Python or Arcade expressions.

SQL expressions are invalid for feature services that are using versioned data.

Are you working with feature services or enterprise geodatabases?  If so, it looks like your original SQL should work with some minor changes because EXTRACT appears supported in the ArcGIS Online Calculate field values—ArcGIS Online Help | ArcGIS documentation (which applies to feature services and also likely EGDB):

The following date functions are available:

Function	Description
CURRENT_DATE()	Returns the current date in UTC time.
CURRENT_TIME()	Returns the current UTC date and time (hours, minutes, seconds).
CURRENT_TIMESTAMP()	Returns the current UTC date and time (hours, minutes, seconds, milliseconds).
EXTRACT(<unit> FROM <date>)	Returns a single part (<unit>) of the specified <date>. Possible<unit> values include but are not limited to year, month, day, hour, and minute. For example EXTRACT(MONTH FROM 12/21/2016)—returns 12. EXTRACT(DAY FROM 12/21/2016 12:00)—returns 21. EXTRACT(HOUR FROM 12/21/2016 15:00)—returns 15.

If you are not working with feature services or enterprise geodatabases, then you would need to use either Arcade or Python.