Challenge #4: Date Parsing

[margiecaina]

my solution

[snothando_hlongwane]

Challenge #4 Solution

[thomasduong]

Here is my solution to Challenge #4

Spoiler

[mark-spain]

A nice regex solution to this date problem. 

Spoiler

I have used the Regex_Match and Regex_Replace functions to identify specific patterns in the data and then pull out only the data I needed to a raw date column. After this, I had 2 different formats of dates, so did a different DateTimeParse operation on each pattern.

Date_raw = 

IF Contains([Field_1], "-")
THEN UpperCase(REGEX_Replace([Field_1], ".*?(\d+-[A-Za-z]+-\d+).*", "$1"))
ELSEIF REGEX_Match([Field_1], ".*?[A-Za-z]{3} \d+,? \d{4}.*")
THEN REGEX_Replace([Field_1], ".*?([A-Za-z]{3}) (\d+),? (\d{4}).*", "$2-$1-$3")
ELSE ""
ENDIF

Date

IF REGEX_Match([Date_raw], "\d+-[A-Z]+-\d{4}")
THEN DateTimeParse([Date_raw], "%d-%b-%Y")
ELSEIF REGEX_Match([Date_raw], "\d+-[A-Z]+-\d{2}")
THEN DateTimeParse([Date_raw], "%d-%b-%y")
ELSE ""
ENDIF