topic inconsistent date format in date field in Talend Studio

inconsistent date format in date field

Anonymous — Sat, 16 Nov 2024 04:28:19 GMT

Hello All,

i'm trying to clean up a date string field that has an inconsistent format.

2019-01-01, 01/01/2019 etc..

in previous ETL tool that i use, i will write up something like this:

IF
Contains([Service Dt],"-") THEN [Service Dt]
ELSEIF
Contains([Service Dt],"/") THEN Regex_Replace([Service Dt],"(\d{1,2})(/)(\d{1,2})(/)(\d{4})","$5-$1-$3")
ELSE
Null()
ENDIF

How do i write this formula in TMap? or is there a better way of doing it?

Thank you in advance.

Re: inconsistent date format in date field

manodwhb — Fri, 04 Oct 2019 10:14:11 GMT

@Schrute_Farms ,you can use the below function in output of enable/Disable filter Expression. the below function will return true or false.here mydate should be string.

TalendDate.isDate(row7.mydate,"yyyy-MM-dd")==true ?row7.mydate:null

Re: inconsistent date format in date field

manodwhb — Fri, 04 Oct 2019 10:20:17 GMT

@Schrute_Farms ,for multiple condition you need to write below way.

TalendDate.isDate(row7.mydate,"yyyy-MM-dd")==true || TalendDate.isDate(row7.mydate,"dd/MM/yyyy")==true ?row7.mydate:null

Re: inconsistent date format in date field

TRF — Fri, 04 Oct 2019 15:46:34 GMT

@Schrute_Farms as I understand, what you want is an expression to unify the output format.

Here is what my suggestion:

TalendDate.isDate(row29.value, "yyyy-MM-dd") ? row29.value : 
	TalendDate.isDate(row29.value, "dd/MM/yyyy") ? row29.value.substring(6) + "-" + row29.value.substring(3, 5) + "-" + row29.value.substring(0, 2) : "Not a valid date"