mysqldatetime

Unexpected behavior with 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? It's the same day so why, when I convert it to datetime, does it return nothing? I get no errors.

Solution

  • 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.

    Try

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

    instead...

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