# 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)==3) {
m = string(arrayIndexOf(months,a)+1)
}
else m = right("0"+a,2)

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

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