MySQL:str_to_date搞砸了一年 [英] MySQL: str_to_date mess up the year

问题描述

我尝试使用str_to_date函数转换timeposted列名称之一(varchar，因为某些数据具有昨天和今天这样的词)，因为我稍后需要检索3个月的时间段，但不知何故它将整年更改为到2020年，并且有时间标记.

I trying to convert one of column name timeposted(varchar because some of the data has word like yesterday and today) using str_to_date function because I need to later retrieve the period of 3 months but somehow it went to change all the year into 2020 with any year found in timeposted.

示例:

1. 09-03-2005转换为09-03-2020
2. 转换为31-03-2020后的2011年9月31日

我的SQL:

select str_to_date(REPLACE(LEFT(timeposted, LOCATE(' ', timeposted)), ',', ''), '%d-%m-%y') from post

我尝试了DATE_FORMAT，但问题仍然相同.您是否有解决此问题的方法，以防止年份发生任何变化.

I tried DATE_FORMAT but the issue still the same. Do you have any workaround for this issue to prevent any changes of year.

替换用于删除'，'，因为发布的时间为"31-02-2010，04:25 PM"

the replace is use to remove ',' as the date found in timeposted is "31-02-2010, 04:25 PM"

谢谢， 基思

推荐答案

您的直接问题是由您在STR_TO_DATE()中使用的格式字符串不正确引起的.

Your immediate problem is caused by the incorrect format string that you used in STR_TO_DATE().

代替

'%d-%m-%y'
       ^^

使用

'%d-%m-%Y'

这里是 SQLFiddle 演示

Here is SQLFiddle demo

现在，正如MarcB所说，您不需要所有这些字符串操作.随便

Now as MarcB already commented you don't need all these string manipulations. Just do

SELECT STR_TO_DATE(timeposted, '%d-%m-%Y') timeposted
  FROM post

这里是 SQLFiddle 演示

Here is SQLFiddle demo

这篇关于MySQL:str_to_date搞砸了一年的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！