ReplaceRegex for Date

[Prasanna9078]

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.

[duristef]

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