ArcPro attribute table field calculator expression:
new_field = standardize_date(!Src_Date!)
# ############# python script #############
from datetime import datetime
import re
def standardize_date(raw_date_string):
#return re.sub('^2020/08/12$','999',raw_date_string) # test works
#return re.sub('\d{4}/\d{2}/\d{2}','999',raw_date_string) # test works
striped_raw_date_string = raw_date_string.strip()
# 1) find date format 2023-10-04
result1 = re.findall('\d{4}-\d{2}-\d{2}', striped_raw_date_string)
# 2) find date format 31/04/2023 or 31/4/2023
result2 = re.findall('(0?[1-9]|[12][0-9]|3[01])\/(0?[1-9]|1[012])\/\d{4}', striped_raw_date_string)
# 3) find date format 4/3/2020 or 04/03/2020
result3 = re.findall('^(0?[1-9]|1[012])\/(0?[1-9]|[12][0-9]|3[01])\/\d{4}', striped_raw_date_string)
# 4) find date format 2021/11/08
result4 = re.findall('\d{4}\/(0?[1-9]|1[012])\/(0?[1-9]|[12][0-9]|3[01])', striped_raw_date_string)
# 5) find date format 2023
result5 = re.findall('^\d{4}$', striped_raw_date_string)
if (result1):
date_time_obj = datetime.strptime(striped_raw_date_string, '%Y-%m-%d')
return date_time_obj.strftime("%Y/%m/%d")
elif (result2):
date_time_obj = datetime.strptime(striped_raw_date_string, '%d/%m/%Y')
return date_time_obj.strftime("%Y/%m/%d")
elif (result3):
date_time_obj = datetime.strptime(striped_raw_date_string, '%m/%d/%Y')
return date_time_obj.strftime("%Y/%m/%d")
elif (result4):
date_time_obj = datetime.strptime(striped_raw_date_string, '%Y/%m/%d')
return date_time_obj.strftime("%Y/%m/%d")
elif (result5):
date_time_obj = datetime.strptime(striped_raw_date_string, '%Y')
return date_time_obj.strftime("%Y/%m/%d")
else:
# empty or null set default valid date
date_time_obj = datetime.strptime('1900-01-01', '%Y-%m-%d')
return date_time_obj.strftime("%Y/%m/%d")
# ##### end ######## python script #############
############## test data ##############
2020
2019/10/16
2020/08/12
31/01/2020
2020-11-03
05/01/2020
04/17/2020
2019/12/31
2021-07-14
2019/12/04
2010/12/16
2021
04/03/2020
4/30/2020
2011/07/18
2017/06/20
2019
2021-10-13
2020
2021-07-14
2021/11/08
2021/1/8
2020-11-03
2021
2019/12/31
2019-12-04
null
2020/01/04