CODEWITHSUNDEEP
mysql
Jonnel VeXuZ Dorotan
Jonnel VeXuZ Dorotan

Reputation: 468

MySQL: DATE_FORMAT Not Working on %m/%d/%Y but Works on %Y/%m/%d

When I execute this query:

SELECT DATE(DATE_FORMAT('2021/09/17 12:00:00 AM', "%Y/%m/%d %h:%i:%s %p"))

This is the result:

9/17/2021

But, when I put %Y on last, and execute this query:

SELECT DATE(DATE_FORMAT('09/17/2021 12:00:00 AM', "%m/%d/%Y %h:%i:%s %p"))

This is the result:

(NULL)

I am expecting to get the result also as 9/17/2021. How can I achieve it? Thanks!

Upvotes: 0

Views: 498

Answers (1)

ysth
ysth

Reputation: 98398

You are using date_format where you should be using str_to_date. date_format produces a formatted string from a date or datetime. str_to_date does the opposite, parsing a formatted string and returning the date or datetime.

Upvotes: 3

Related Questions