从字符串字段转换 MySQL 中的日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1861489/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Converting a date in MySQL from string field
提问by DisgruntledGoat
I'm using a system where the dates are stored as strings in the format dd/mm/yyyy. Is it possible to convert this to yyyy-mm-ddin a SELECT query (so that I can use DATE_FORMATon it)? Does MySQL have a date parsing function?
我正在使用一个系统，其中日期以dd/mm/yyyy. 是否可以将其转换yyyy-mm-dd为 SELECT 查询（以便我可以使用DATE_FORMAT它）？MySQL 有日期解析功能吗？
Currently the only method I can think of is to concatenate a bunch of substrings, but hopefully there's a simpler solution.
目前我能想到的唯一方法是连接一堆子字符串，但希望有一个更简单的解决方案。
(Unfortunately I can't convert the field to a true date field since it's a meta-table: the same column contains values for different fields that are just strings.)
（不幸的是，我无法将该字段转换为真正的日期字段，因为它是一个元表：同一列包含只是字符串的不同字段的值。）
回答by OMG Ponies
This:
这个：
STR_TO_DATE(t.datestring, '%d/%m/%Y')
...will convert the string into a datetime datatype. To be sure that it comes out in the format you desire, use DATE_FORMAT:
...将字符串转换为日期时间数据类型。为了确保它以您想要的格式出现，请使用DATE_FORMAT：
DATE_FORMAT(STR_TO_DATE(t.datestring, '%d/%m/%Y'), '%Y-%m-%d')
If you can't change the datatype on the original column, I suggest creating a viewthat uses the STR_TO_DATEcall to convert the string to a DateTime data type.
如果您无法更改原始列上的数据类型，我建议创建一个使用STR_TO_DATE调用将字符串转换为 DateTime 数据类型的视图。
回答by nos
Yes, there's str_to_date
是的，有str_to_date
mysql> select str_to_date("03/02/2009","%d/%m/%Y");
+--------------------------------------+
| str_to_date("03/02/2009","%d/%m/%Y") |
+--------------------------------------+
| 2009-02-03 |
+--------------------------------------+
1 row in set (0.00 sec)
回答by monksy
STR_TO_DATEallows you to do this, and it has a format argument.
STR_TO_DATE允许你这样做，它有一个格式参数。
回答by h4rp0
SELECT STR_TO_DATE(dateString, '%d/%m/%y') FROM yourTable...
