MySQL 更新mysql中的日期格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4259729/
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
updating a date format in mysql
提问by Tom Ostlund
I am working on a table that has two formats of dates stored in a field some are in mm/dd/yy and the newer entries are in yyyy/mm/dd like they should be.
我正在处理一个表，该表在一个字段中存储了两种格式的日期，一些在 mm/dd/yy 中，而较新的条目在 yyyy/mm/dd 中，就像它们应该的那样。
I want to run an query like this
我想运行这样的查询
UPDATE table
SET date_field = DATE_FORMAT(date_field, '%Y/%m/%d')
WHERE date_field = DATE_FORMAT(date_field, '%m/%d/%y')
But it is just not working out. One result that I got was that it was just taking the %m data and turning it into the %Y and really messing up the data.
但它只是行不通。我得到的一个结果是，它只是将 %m 数据转换为 %Y 并真正弄乱了数据。
Any thoughts?
有什么想法吗？
回答by Vincent Savard
You want to use STR_TO_DATE function, not DATE_FORMAT. Plus, I assume you only want to update the misformed dates, so I guess you could do this :
您想使用 STR_TO_DATE 函数，而不是 DATE_FORMAT。另外，我假设您只想更新格式错误的日期，所以我想您可以这样做：
UPDATE your_table
SET date_field = DATE(STR_TO_DATE(date_field, '%m/%d/%Y'))
WHERE DATE(STR_TO_DATE(date_field, '%m/%d/%Y')) <> '0000-00-00';
P.S. Tables contain columns, not fields. And you shouldn't use a string type to hold your dates, but the DATE type
PS 表包含列，而不是字段。并且您不应该使用字符串类型来保存日期，而是使用 DATE 类型
回答by Ben Lee
You want to use STR_TO_DATEto first convert the incorrect string to a real date object, then use DATE_FORMATto turn it back into a string of the format you want.
您想STR_TO_DATE先将不正确的字符串转换为实际日期对象，然后DATE_FORMAT再将其转换回您想要的格式的字符串。
Also, your WHEREcondition won't work like that. You should be looking for strings of the wrong format using LIKE.
此外，你的WHERE情况不会像那样工作。您应该使用LIKE.
UPDATE your_table SET date_field =
DATE_FORMAT(STR_TO_DATE(date_field, '%m/%d/%y'), '%Y/%m/%d')
WHERE date_field LIKE '__/__/____'
回答by Piskvor left the building
Thoughts? Ah yes, three actually:
想法？啊，是的，实际上是三个：
don't reinvent the wheel. Yes, yes, I know it's tempting to do from scratch what only a million of people have invented before (no sarcasm intended), but MySQL has a good, efficient, and usable
DATEformat, use that; only do the formatting on the input/output. That lets you avoid these kind of mixups of data (the date itself) and presentation (date format).As for the data, restore the known-good backup (you have backups, right?) - in the general case, there's no fool-proof way to tellunconverted y/m/d from already-converted m/d/y and the dates are f***ed for any practical purposes. (especially not in 2010 - e.g.
08/10/09can mean several valid dates).Also, what kind of idea is it to only store 2 digits of the date? That's exactly the kind of short-sighted penny-pinching that has brought about the time- and money-sink that was Y2K. (avoided if you'd store the date, y'know, in a date format).
不要重新发明轮子。是的，是的，我知道从头开始做以前只有一百万人发明的东西是很诱人的（没有讽刺的意思），但是 MySQL 有一个很好的、高效的和可用的
DATE格式，使用它；只对输入/输出进行格式化。这可以让您避免这种数据（日期本身）和表示（日期格式）的混淆。至于数据，恢复已知良好的备份（你有备份，对吗？） - 在一般情况下，没有万无一失的方法来区分未转换的 y/m/d 和已经转换的 m/d/y 以及出于任何实际目的，日期都是他妈的。（尤其不是在 2010 年 - 例如
08/10/09可能意味着几个有效日期）。另外，只存储日期的 2 位数字是什么想法？这正是导致 Y2K 时间和金钱沉没的那种短视的吝啬。（如果您要以日期格式存储日期，请避免使用）。
