如何在 MySQL 中将字符串转换为日期?

2021-11-20 00:00:00 string date mysql

我有一个 string 列作为 date 并且我想选择它作为 DATE.

I have a string column which acts as a date and I want to select it as a DATE.

有可能吗?

我的示例数据格式是:

月/日/年 ->12/31/2011

推荐答案

正如在 MySQL 使用带有日期文本的字符串列作为日期字段，你可以这样做

As was told at MySQL Using a string column with date text as a date field, you can do 

SELECT  STR_TO_DATE(yourdatefield, '%m/%d/%Y')
FROM    yourtable

您还可以在 WHERE 子句中处理这些日期字符串.例如

You can also handle these date strings in WHERE clauses. For example

SELECT whatever
  FROM yourtable
 WHERE STR_TO_DATE(yourdatefield, '%m/%d/%Y') > CURDATE() - INTERVAL 7 DAY

您可以通过这种方式处理各种日期/时间布局.请参考格式说明符DATE_FORMAT() 函数 看看你可以把什么放入 STR_TO_DATE() 的第二个参数中.

You can handle all kinds of date/time layouts this way. Please refer to the format specifiers for the DATE_FORMAT() function to see what you can put into the second parameter of STR_TO_DATE().

相关文章