# ReplaceRegex for Date

Hi,

i have some many date format variation like below,
yyyy-mm-dd
yy-mm-dd
yy-mmm-d
d-mm-yy
d-mmm-yyyy
d/mm/yyy
yyyy/mmm/d

i need to convert these to yyyy-mm-dd. please advise the replaceregex for that.

hi @Prasanna9078
Unfortunately, no algorithm can solve this problem, because some dates are ambiguous. Think for example of “22/12/01”. It could mean “2022/12/01” or “2001/12/22”
This column expression works in many cases

``````months = arrayCreate("jan", "feb", "mar", "apr", "may", "jun", "jul","aug", "sep", "oct", "nov", "dec")
y = missingValue()
d = missingValue()
a = split(regexReplace(column("dates"), "\\W", "-"),"-")

if(length(a[1])==3) {
m = string(arrayIndexOf(months,a[1])+1)
}
else m = right("0"+a[1],2)

if( or(length(a[0])==4, toInt(a[0])>31)) {
y = a[0]
d = a[2]
}
else if ( length(a[2])>=3) {
y = right("1"+a[2],4)
d = a[0]
}
else if ( toInt(a[2])>31) {
y = a[2]
d = a[0]
}
else if (length(a[0])==1) {
y = a[2]
d = a[0]
}
else if (length(a[2])==1) {
y = a[0]
d = a[2]
}

if(or(isMissing(y), isMissing(d))) {
missingValue()
}
else joinSep("-", right("20"+y,4), right("0"+m,2), right("0"+d,2))
``````

but not always: as you can see, 22/12/01 is considered ambiguous

4 Likes

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.