How to standardize date format use ArcPro ( part 1 ) You can't do with esri date column

Question

 You can't do with esri Date column 

Here is why

 

Users input date as whatever they like, today they input 2023-4-28, tomorrow they forget what format they use before, they then input 4/29/2023.  Other users may input 30/4/2023,  or  04/30/2023, or 30-4-2023 or 04-30-2023 or just input month only 2023/4,  or just input year only 2023. 

 

https://transparentgov.net:3200/googlemaps141/default?layer_id=0&layer=NYPAD2_Easement&center_lat=41.18333803421051&center_long=-73.7791579159306&center_zoom=13&url=https%3A%2F%2Fservices6.arcgis.com%2FDZHaqZm9cxOD4CWM%2FArcGIS%2Frest%2Fservices%2FNYPAD2_Easement%2FFeatureServer&panto=0&overlayType=overlayType_none&select_field=14&select_fieldvalue=-1&distinctorgroupby=groupby

 

 

https://transparentgov.net:3200/googlemaps141/default?layer_id=0&layer=NYPAD_2_Fee&center_lat=43.07607440739406&center_long=-74.21026135147456&center_zoom=19&url=https%3A%2F%2Fservices6.arcgis.com%2FDZHaqZm9cxOD4CWM%2FArcGIS%2Frest%2Fservices%2FNYPAD_2_Fee%2FFeatureServer&panto=0&overlayType=overlayType_none&select_field=14&select_fieldvalue=-1&distinctorgroupby=groupby

 

This exactly happen in New York State protected land layers. 

Above example, source date column (Src_date) is text field, char(15). Not  esri 'date' type. This is very common in other states and county as well. They collect data from different source, date format is totally different is not unusual. 

 

 

I am trying to standardize these date format using ArcPro. 

 

Step 1) Download above web service layer into local hard drive. Search my other post on How To download web service layer.

 

Step 2) Show possible date format in source date (Src_Date) column

 

 

 

 

 

 

 

 

Step 3) Convert source date (Src_Date) column from text field to ESRI date field

ESRI document is here :    Convert string or numeric time values into date format

https://pro.arcgis.com/en/pro-app/latest/help/mapping/time/convert-string-or-numeric-time-values-into-data-format.htm

https://pro.arcgis.com/en/pro-app/latest/tool-reference/data-management/convert-time-field.htm

 

 

But, with $3000 FME, then  Yes 

https://www.g2.com/products/fme-platform/pricing

How to do that with FME, search my other post

 

You can't do in esri Date column

But you can do in text field. 

You can do with python regex in ArcPro (requir $$$)

You can do with python regex in Qgis (free)