MySQL 查询sql将文本字段dd/mm/yyyy转换为日期字段yyyy-mm-dd
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4143476/
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
query sql convert text field dd/mm/yyyy to date field yyyy-mm-dd
提问by stefano
i've imported a CSV file into mysql with dates dd/mm/yyyy Now need a query to convert it from text to date format yyy-mm-dd
我已将日期为 dd/mm/yyyy 的 CSV 文件导入 mysql 现在需要查询将其从文本转换为日期格式 yyy-mm-dd
Cheers
干杯
S.
S。
回答by conny
You could use the STR_TO_DATE(str, format)MySQL function.
您可以使用STR_TO_DATE(str, format)MySQL 函数。
Example switching out my_date_colfor a converted one:
转换my_date_col为转换后的示例：
BEGIN;
ALTER TABLE `date_test`
ADD COLUMN `my_date_col_converted` DATE;
UPDATE `date_test`
SET `my_date_col_converted` = STR_TO_DATE(`my_date_col`, '%d/%c/%Y');
ALTER TABLE `date_test`
DROP COLUMN `my_date_col`;
ALTER TABLE `date_test`
CHANGE COLUMN `my_date_col_converted` `my_date_col` DATE;
COMMIT;
回答by DMA57361
You can use STR_TO_DATE()in the following way to convert your text in to a DATE:
您可以通过STR_TO_DATE()以下方式将文本转换为 a DATE：
STR_TO_DATE( datefield , "%d/%m/%Y" )
If you need this DATEin a specific format, you can use DATE_FORMAT().
This probably isn't necessary in your case, but here's an example for completeness:
如果您需要DATE特定格式的文件，您可以使用DATE_FORMAT().
在您的情况下，这可能不是必需的，但这里有一个完整的示例：
DATE_FORMAT( STR_TO_DATE( datefield , "%d/%m/%Y" ) , "%Y/%m/%d" )
So, you could do this over the whole table with a single UPDATEto replace the current data with the reformatted data (while keeping the datatype the same):
因此，您可以使用单个UPDATE对整个表执行此操作，用重新格式化的数据替换当前数据（同时保持数据类型相同）：
UPDATE tableName
SET originalDate = DATE_FORMAT(STR_TO_DATE(originalDate,"%d/%m/%Y" ),"%Y/%m/%d" );
Or, if you want to convert the datatype of the column DATEyou could alter the table to create a new DATEformatted column, use the above update to fill that column, remove the original column, and then (optionally) rename the new column to the old name.
或者，如果要转换列的数据类型，DATE可以更改表以创建新的DATE格式化列，使用上述更新填充该列，删除原始列，然后（可选）将新列重命名为旧列姓名。
ALTER tableName
ADD modifiedDate DATE;
UPDATE tableName
SET modifiedDate = DATE_FORMAT( STR_TO_DATE( originalDate ,"%d/%m/%Y" ) ,"%Y/%m/%d" );
ALTER tableName
DROP COLUMN originalDate;
ALTER tableName
CHANGE COLUMN modifiedDate originalDate;
回答by greyghostie
This should work but it doesn't:
这应该有效，但无效：
BEGIN;
ALTER TABLE `date_test`
ADD COLUMN `my_date_col_converted` DATE;
UPDATE `date_test`
SET `my_date_col_converted` = STR_TO_DATE(`my_date_col`, '%d/%c/%Y');
ALTER TABLE `date_test`
DROP COLUMN `my_date_col`;
ALTER TABLE `date_test`
CHANGE COLUMN `my_date_col_converted` `my_date_col` DATE;
COMMIT;
Also this should work: Doesn't Work
这也应该有效：不起作用
UPDATE db_test SET anticipated_court_date = DATE_FORMAT(STR_TO_DATE(anticipated_court_date,"%d/%m/%Y" ),"%Y-%m-%d" );
Server version: 5.0.95-community-log MySQL Community Edition (GPL)
服务器版本：5.0.95-community-log MySQL Community Edition (GPL)
However this works:
但是这有效：
SELECT STR_TO_DATE('8/31/12', '%m/%d/%Y'); // WORKS
Using MySQL - I couldn't find any solution that worked reliably. Even the same exact date wasn't converted successfully.
使用 MySQL - 我找不到任何可靠的解决方案。即使是相同的确切日期也没有成功转换。
The solution I've found is this: PHP
$user_date = "8/31/12"; // WORKS
$mysql_date = date('Y-m-d H:i:s', strtotime($user_date));
回答by Waqas Bukhary
The above were helpful, but the following worked for me on Mysql.
以上内容很有帮助，但以下内容在 Mysql 上对我有用。
ALTER TABLE `tablename` ADD `newcolumn` DATE NOT NULL ;
UPDATE `tablename` SET `newcolumn`=STR_TO_DATE(`oldcolumn`, '%d/%c/%Y')
Now delete the oldcolumn (if you wish).
现在删除旧列（如果您愿意）。
