MySQL 如何在mysql中将字符串转换为日期？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5201383/
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
how to convert a string to date in mysql?
提问by Joseph Lafuente
I have a stringcolumn which acts as a dateand I want to select it as a date.
我有一个string充当a 的列，date我想将其选择为date.
Is it possible?
是否可以？
My sample data format would be; month/day/year-> 12/31/2011
我的示例数据格式是；month/day/year->12/31/2011
回答by bluefoot
As was told at MySQL Using a string column with date text as a date field, you can do
正如MySQL所说的使用带有日期文本的字符串列作为日期字段，您可以执行
SELECT STR_TO_DATE(yourdatefield, '%m/%d/%Y')
FROM yourtable
You can also handle these date strings in WHEREclauses. For example
您还可以在WHERE子句中处理这些日期字符串。例如
SELECT whatever
FROM yourtable
WHERE STR_TO_DATE(yourdatefield, '%m/%d/%Y') > CURDATE() - INTERVAL 7 DAY
You can handle all kinds of date/time layouts this way. Please refer to the format specifiers for the DATE_FORMAT()functionto see what you can put into the second parameter of STR_TO_DATE().
您可以通过这种方式处理各种日期/时间布局。请参阅函数的格式说明符，DATE_FORMAT()以了解您可以将什么放入STR_TO_DATE().
回答by Bala R
STR_TO_DATE('12/31/2011', '%m/%d/%Y')
回答by Bharathiraja
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html
use the above page to refer more Functions in MySQL
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html
使用上面的页面参考更多 MySQL 中的函数
SELECT STR_TO_DATE(StringColumn, '%d-%b-%y')
FROM table
say for example use the below query to get output
说例如使用下面的查询来获取输出
SELECT STR_TO_DATE('23-feb-14', '%d-%b-%y') FROM table
For String format use the below link
对于字符串格式，请使用以下链接
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format
回答by aalhanane
Here's another two examples.
这是另外两个例子。
To output the day, month, and year, you can use:
要输出日、月和年，您可以使用：
select STR_TO_DATE('14/02/2015', '%d/%m/%Y');
Which produces:
其中产生：
2015-02-14
2015-02-14
To also output the time, you can use:
要同时输出时间，您可以使用：
select STR_TO_DATE('14/02/2017 23:38:12', '%d/%m/%Y %T');
Which produces:
其中产生：
2017-02-14 23:38:12
2017-02-14 23:38:12
