使用mysql查询将日期从“yyyyy -dd-mm”转换为“yyyyy -mm-dd”

I am working on a database which has date fields in 'yyyy-dd-mm hh:mm' format, I used STR_TO_DATE to change the field to 'yyyy-mm-dd hh:mm' but it is giving back an error.

我正在开发一个数据库，它有“yyyyyy -dd-mm:mm”格式的日期字段，我使用STR_TO_DATE将字段更改为“yyyyyyyy -mm-dd -dd-mm:mm”，但它返回了一个错误。

Query:

查询:

UPDATE transaction 
SET time_creation = STR_TO_DATE(time_creation, '%Y-%m-%d %H:%i');

Error:

错误:

Incorrect datetime value: '2005-08-06 15:57:00' for function str_to_date

不正确的日期时间值:函数str_to_date的“2005-08-06 15:57:00”

I did a check with the following query too:

我也做了一个查询:

SELECT 
   time_creation, 
   STR_TO_DATE(time_creation,'%Y-%m-%d %H:%i') AS DATE_FORMATTED 
FROM transaction;

and got NULL in the DATE_FORMATTED column for date values such as '2007-22-11 15:32' but worked fine for '2007-09-11 13:12'. I don't understand what exactly is happening. Any help is appreciated.. thank you.

在date_formatting列中为日期值设置为NULL，比如“2007-22-11 15:32”，但是在“2007-09-11 13:12”中可以正常工作。我不明白到底发生了什么。任何帮助都是欣赏. .谢谢你！

2 个解决方案

#1

First, you need to share the table definition with us. It sounds like the column type for the time_creation field is a VARCHAR and not a DATETIME. So you really should not try to store the result back into the same field. You want to save it into a valid DATETIME field so that the intrinsic column type is correct.

首先，您需要与我们共享表定义。它听起来像time_creation字段的列类型是VARCHAR而不是DATETIME。因此，你真的不应该试图将结果存储回相同的字段中。您希望将它保存到一个有效的DATETIME字段中，以便内部列类型是正确的。

Second, it sounds like some of your values are stored as YYYY-DD-MM and some as YYYY-MM-DD. Is that the case? If it is, you'll need more logic in order to differentiate between the values that are already correct and those that are not. If they are mixed, however, you are in a bit of a pickle because how do you know the proper way to interpret 2012-01-03?

其次，您的一些值似乎被存储为yyyyyy - dd，而有些则存储为YYYY-MM-DD。是这样吗?如果是的话，您将需要更多的逻辑来区分已经正确的值和不正确的值。但是，如果它们是混合的，那么你就有点麻烦了，因为你怎么知道解释2012-01-03的正确方式呢?

EDIT:

编辑:

Create a new DATETIME field (for the example I just use new_time_creation) and update like this:

创建一个新的DATETIME字段(例如，我只使用new_time_creation)，并进行如下更新:

UPDATE transaction SET new_time_creation = STR_TO_DATE(time_creation, '%Y-%d-%m %H:%i');

#2

You have

你有

STR_TO_DATE(time_creation,'%Y-%m-%d %H:%i')

and you give '2007-22-11 15:32' which means 'yyyy-dd-*mm* hh:mm'.

你给出的是“yyyyyyy -dd-*mm:mm”的意思。

Sql is trying to parse a date that contains the number 22 as month...

Sql试图解析包含数字22的日期为月……

Abyway

SELECT STR_TO_DATE('2005-08-06 15:57:00', '%Y-%m-%d %H:%i');

works fine in my mysql server.

在我的mysql服务器上运行良好。

#1