STR_TO_DATE（）出现意外行为

The following returns a bunch of row from my DB:

以下命令返回我的数据库中的一堆行:

USE test;
SELECT * FROM aaaa
WHERE dddd BETWEEN (NOW() - INTERVAL 42 DAY) AND (NOW() + INTERVAL 42 DAY)

But for reasons I do not understand the following query returns nothing:

但由于原因,我不明白以下查询不返回任何内容:

USE test;
SELECT * FROM aaaa
WHERE dddd BETWEEN (STR_TO_DATE('2014/6/6', '%Y,%m,%d') - INTERVAL 42 DAY) AND (STR_TO_DATE('2014/6/6', '%Y,%m,%d') + INTERVAL 42 DAY)

Shouldn't it return a similar number of rows? Its the same day so why, when I convert it to datetime, does it return nothing? I get no errors.

它不应该返回相似数量的行吗?它是同一天所以为什么,当我把它转换为datetime时,它什么都没有返回?我没有错。

2 个解决方案

#1

You're passing in a /-separated date, but telling MySQL it's ,-separated. Since you have no , in your string, the format doesn't apply, and you end up with an invalid date.

你正在传递一个/分离的日期,但告诉MySQL,它是分离的。由于您没有,在您的字符串中,格式不适用,并且您最终得到无效日期。

Try

STR_TO_DATE('2014/6/6', '%Y/%m/%d')

instead...

"invalid - 42 days = invalid", not something 42 days ago.

“无效 - 42天=无效”,而不是42天前。

#2

You should use

你应该用

STR_TO_DATE('2014/6/6', '%Y/%m/%d')

Note the / instead of , in the date format. ;)

请注意日期格式中的/而不是。 ;)

#1