MySQL查询中DATE_FORMAT日期转换失效的原因及解决办法

Let's break down exactly what's going wrong here, then fix it step by step.

The Root Cause #

Your date conversion is failing because the format mask you're using in STR_TO_DATE doesn't match the structure of your input date string.

Here's the problematic line again:

STR_TO_DATE("03/15/2018", '%d/%m/%Y')

• The input string "03/15/2018" follows a MM/DD/YYYY (month/day/year) pattern
• But your mask %d/%m/%Y tells MySQL to expect DD/MM/YYYY (day/month/year)

When MySQL tries to parse "03/15/2018" with %d/%m/%Y, it tries to interpret 15 as the month—which is invalid (there's no 15th month). This makes STR_TO_DATE return NULL, and DATE_FORMAT(NULL, '%Y-%m-%d') also returns NULL. Comparing any valid date to NULL always evaluates to false, so your query returns 0 every time.

The Fix #

First, use the correct format mask for your input date. Since "03/15/2018" is MM/DD/YYYY, swap the mask to %m/%d/%Y.

Second, you don't even need DATE_FORMAT! STR_TO_DATE already outputs a proper DATE type, which can be directly compared to your MCPS.price_date field (assuming it's stored as a DATE or DATETIME type).

Here's your corrected date condition:

AND MCPS.price_date >= STR_TO_DATE("03/15/2018", '%m/%d/%Y')
AND MCPS.price_date <= STR_TO_DATE("03/15/2018", '%m/%d/%Y')

Or, to simplify checking for a single exact date, you can just write:

AND MCPS.price_date = STR_TO_DATE("03/15/2018", '%m/%d/%Y')

Extra Recommendations #

• Verify your price_date field type: If it's stored as a VARCHAR instead of a DATE/DATETIME, consider altering the table to use the proper date type. This eliminates formatting mismatches entirely and makes date operations faster and more reliable.
• Align input and stored formats: If your stored dates use DD/MM/YYYY (like 15/03/2018), make sure any input dates you pass in follow the same pattern, or adjust the STR_TO_DATE mask accordingly.

内容的提问来源于stack exchange，提问作者AndreaNobili