LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: mlr

Hi,

I have a huge dataset (~20M records) where in a column 'Closing_Date' has date values in different formats. The data source is in a csv file, which I'm reading via csv node and the column has type of string.

I want to convert all these different formats into "MM/DD/YYYY" format. Is there an inbuilt function or if anybody has examples to achieve this? I couldn't get the date() function to perform this.

The need to convert is that later in the flow, I need to validate if that date falls between a certain date range.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ThomasT

This is very messy so I guess the Lavastorm guys have a better solution, but this should work


#corrects the year to 4 digits
if Closing_Date.right(4).left(2) == "20" then P = Closing_Date else if
Closing_Date.right(3).left(1) == "-" then P = Closing_Date.left(Closing_Date.len()-3)+"-20"+Closing_Date.right(2) else
P = Closing_Date.left(Closing_Date.len()-4)+"-20"+right(Closing_Date,2)

#replaces the letters to the corresponding month
if P.strFind("JAN") > -1 then X = P.replace("JAN","1") else if
P.strFind("FEB") > -1 then X = P.replace("FEB","2") else if
P.strFind("MAR") > -1 then X = P.replace("MAR","3") else if
P.strFind("APR") > -1 then X = P.replace("APR","4") else if
P.strFind("MAY") > -1 then X = P.replace("MAY","5") else if
P.strFind("JUN") > -1 then X = P.replace("JUN","6") else if
P.strFind("JUL") > -1 then X = P.replace("JUL","7") else if
P.strFind("AUG") > -1 then X = P.replace("AUG","8") else if
P.strFind("SEP") > -1 then X = P.replace("SEP","9") else if
P.strFind("OCT") > -1 then X = P.replace("OCT","10") else if
P.strFind("NOV") > -1 then X = P.replace("NOV","11") else if
P.strFind("DEC") > -1 then X = P.replace("DEC","12") else
X = P


#convert do date depending on the format
if X.strFind("/") > -1 then Newdate = date(X,"M/D/CCYY") else Newdate = date(X,"D-M-CCYY")

#emit new field
emit * override emit Newdate as "Closing_Date"

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: andycooper

Thanks for the response Thomas, there is indeed a shorter way of achieving the conversion. If you paste the details below into a filter node this will convert all the values into the correct format.

if 'Closing_Date'.isDate("D/M/CCYY") then
newDate = 'Closing_Date'.date("D/M/CCYY")else
if 'Closing_Date'.isDate("M/D/CCYY") then
newDate = 'Closing_Date'.date("M/D/CCYY")else
if 'Closing_Date'.isDate("D-m-CCYY") then
newDate = 'Closing_Date'.date("D-m-CCYY")else
if 'Closing_Date'.isDate("D-m-YY") then
newDate = 'Closing_Date'.date("D-m-YY")else
newDate = null #or whatever you want to do with dates that cannot be converted.
emit *, newDate

If you encounter any additional date formats then you would need to add additional lines to the filter script. This also includes an else statement to add "NULL" to values it can't convert so you can easily find records that need checking.

Ta

Andy

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: mlr

Thanks to both Thomas & Andy for your help. I was able to get the dates in desired format.