在mysql中更新日期格式 [英] updating a date format in mysql

问题描述

我正在处理一个表，该表具有两种格式的日期存储在字段中，其中一些格式应为mm/dd/yy，而较新的条目则应为yyyy/mm/dd.

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.

我要运行这样的查询

UPDATE table
   SET date_field = DATE_FORMAT(date_field, '%Y/%m/%d') 
 WHERE date_field = DATE_FORMAT(date_field, '%m/%d/%y')

但是它还没有解决.我得到的一个结果是，它只是获取％m数据并将其转换为％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.

有什么想法吗?

推荐答案

您要使用STR_TO_DATE函数，而不要使用DATE_FORMAT.另外，我假设您只想更新格式错误的日期，所以我想您可以这样做:

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 :

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.表格包含列，而不包含字段.而且，您不应该使用字符串类型来保存日期，而应使用DATE类型

P.S. Tables contain columns, not fields. And you shouldn't use a string type to hold your dates, but the DATE type

这篇关于在mysql中更新日期格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！