Converting String to Date is off by a Day

Question

When I try to convert a string to a date in MYSQL, I'm getting weird results.

SELECT 
    '2017-06-20' d
    ,CAST('2017-06-20' as DATE) d1
    ,DATE_FORMAT('2017-06-20', '%Y-%m%-%d') d2 
    ,STR_TO_DATE('2017-06-20', '%Y-%m-%d') d3
    ,DATE('2017-06-20') d4

On my machine, d2 is the only correct date, i.e. it generates 2017-06-20. The rest, i.e. d1, d3 and d4, generate 06/19/2017. Why? I'm especially perplexed by STR_TO_DATE

Possible dup / explanation... https://stackoverflow.com/questions/40774904/why-is-my-date-from-mysql-decrementing-by-one-day-in-javascript — SS_DBA, Aug 09 '19 at 17:29
What version of mysql, and what are your time zone settings? `SHOW VARIABLES LIKE '%zone';` — Hartmut Holzgraefe, Aug 09 '19 at 17:53
This had to do with Toad, the editor I was using. For some, it was displaying a different time than what was actually in the database. Not sure why an editor would do this, but be weary of Toad. — Jacob H, Oct 15 '19 at 19:50

score 1 · Accepted Answer · answered Aug 09 '19 at 18:32

You are confused about data types. MySQL has built-in data types. It will recognize 'YYYY-MM-DD' as a string.

When the result of an expression is a string, it is put into the result set as-is.

When the result is a date/time, then it is shown based on your local settings.

So, DATE_FORMAT() is interpreting the first argument as a date and converting it to a string. That is what you get.

On the other hand, STR_TO_DATE() does what the name implies. It converts the first argument -- a string -- to a date. That is then presented based on your local settings.

Converting String to Date is off by a Day

1 Answers1