MySQL question. Struggling to convert text string in correct date format

Question

I have a column named strdate in MySQL database with a table named data. The strdate column is of type varchar(100) I tried a couple of ways to use the select command to get results in a legitimate date format. When I succeed I want to update that column to a proper date type.

I started with this command: SELECT DATE_FORMAT(STR_TO_DATE(strdate, '%e %M Y, %h %m'), '%H:%i') FROM data where topic = 26801;

But that gave me this un-successfull result:

Here are a couple of examples in the strdate column which I want to convert:

7 juni 2016, 22:04

19 juli 2011, 20:02

9 augustus 2022, 06:07

I read through this website: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format

I assume that I have to write the comma and the colon in a literary way in the query. To summarize: How to compose the query in a way to select the dates in the given example (which are text strings) and convert them to a valid and proper date format. If that goes well I want to use the update command to adjust the type of the strdate column and insert the dates as real dates. Any help would be really appriciated.

Answer 1

Dutch month names can be output by DATE_FORMAT(), but unfortunately MySQL does not support the reverse for STR_TO_DATE():

https://dev.mysql.com/doc/refman/8.0/en/locale-support.html

The locale indicated by the lc_time_names system variable controls the language used to display day and month names and abbreviations. This variable affects the output from the DATE_FORMAT(), DAYNAME(), and MONTHNAME() functions.

lc_time_names does not affect the STR_TO_DATE() or GET_FORMAT() function.

This feature to parse locale-specific month names was requested in this bug reported in 2012: https://bugs.mysql.com/bug.php?id=64023. You might like to click the "Affects Me" button for that bug, to try to raise the priority of implementing it.

In the meantime, I'm afraid you'll have to parse non-"en_US" month names yourself.

---

Here's a solution showing replacing three month names. For all 12 months, you would need more nested REPLACE() function calls. It's ugly, but it would work.

SELECT STR_TO_DATE(
  REPLACE(REPLACE(REPLACE(strdate, 'juni', 'June'), 'juli', 'July'), 'augustus', 'August'), 
  '%e %M %Y, %H:%i') AS `date`
FROM data
WHERE topic = 26801;

Output, tested in MySQL 8.3.0:

+---------------------+
| date                |
+---------------------+
| 2016-06-07 22:04:00 |
| 2011-07-19 20:02:00 |
| 2022-08-09 06:07:00 |
+---------------------+