String to Date Replace

[kilincali35]

I have a dataset having a string column like “Nov 01, 2018”

I need to replace it to time format. I have 2 questions

1-) String to Time node can’t recognize this format, I tried my best but couldn’t figure it out. Is there a short way for it?

2-)Then I divided it into 3 seperate columns like Month, Day, Year by using substr from “Column Expressions” node

I also needed to replace “Jan” with “01”, “Feb” with “02”… etc. Then concatenate as a format like yyyy-MM-dd

I used “Rule Engine” to replace these.

Is there another way of doing this multiple replace operation? Column Expressions node couldn’t make it even though I defined 12 rule seperately, only one rule worked, others stayed same.

Thanks for your time,
Have a nice day

[oole]

Hi @kilincali35

I threw together a small workflow. It first splits the string date into its parts, then uses a String Replace (Dictionary) node to replace the months. Then those columns are re-combined and the string is converted:

Here is the workflow: date_dictionary_replace.knwf (16.0 KB)

[dnaki]

@oole I guess I wasn’t fast enough with my reply :-).

Anyway, since I started responding, I’ll post my solution…
The pattern to parse the given date format is “MMM dd, yyyy”.
(see https://www.journaldev.com/17899/java-simpledateformat-java-date-format for java date time patterns)
You can use String to Date&Time node using that pattern.
Then extract any part of the date/time using the Extract Date&Time Fields node.
From there it’s easy to concatenate the fields you want (not shown - see Column Combiner e.g. in latter part of oole’s workflow).

[oole]

@dnaki and you should post it! It’s way simpler than what I did Sometimes reading some documentation really would pay off!