how to convert a string to date in MySQL 5.7

Question

Now I have a data string like this 2002年05月11日, I want to convert this string as a unix date timestamp, what should I do to convert this time? I have tried this way:

select STR_TO_DATE("2002年05月11日", "YYYY年MM月dd日")

did not work. Then I tried this:

select STR_TO_DATE("2002年05月11日", "%Y年%M月%d日")

still did not work. what should I do to convert it to date format that could understand by database?

• Akina: Trivial inaccuracy. %M is not a pattern for 2-digit month number.

Answer 1

Use lower case 'm':

mysql> select STR_TO_DATE("2002年05月11日", "%Y年%m月%d日");
+-----------------------------------------------------+
| STR_TO_DATE("2002年05月11日", "%Y年%m月%d日") |
+-----------------------------------------------------+
| 2002年05月11日 |
+-----------------------------------------------------+