How do I convert date strings such as "1 September 1899" to "1899-09-01"

Question

I want to convert strings like "1 September 1899" to "1899-09-01" ("1 July 2020" to "2020--07-01"). How do I do this?

value.toString("dd/MM/YYYY") which of course did not work and date_parse_from_format(value,'M jS Y', $date_str) failed also

None of the answers posted worked.

Answer 1

In OpenRefine a value like "1 September 1899" has no further meaning. But you can transform this text to a date type using toDate. Then you can use certain components from the date (like year, month, ...) to form a new string using the toString function.

The following GREL expression will just do that:

value.toDate("dd MMMM yyyy").toString("yyyy-MM-dd")

You may also use multiple formats in the toDate function to convert a column with mixed date formats to a common format. Details on the meaning of e.g. yyyy can be found in the documentation for the SimpleDateFormat class.