phpmyadmin Question posted in
The official documentation can be found here.

MySQL DATE_FORMAT "%d" issue – Phpmyadmin

I’m sure there’s a logic to this, but I do not understand it

date_format(date, '%m.%d.%Y') // Output: 03.05.2021
date_format(date, '%D.%m.%Y') // Output: 5th.03.2021
date_format(date, '%e.%m.%Y') // Output: 5.03.2021

But

date_format(date, '%d.%m.%Y') // Output: 28.02.2021

There seems to be an issue when I start the date_format with "%d", but I need my output to be 05.03.2021 (which is 5th March 2021), but I’m not able to achieve that. Can someone please explain this logic to me and help me?

Updated Explanation: In my database I have data from 5th March 2021 to 17th February 2021, so when I SELECT from the table using date_format(date_column, ‘%d.%m.%Y’), and order by date_column desc, the table begins from 28.02.2021 instead of 05.03.2021

Tags:

2

Answers


  1. 0

    You need to convert the date which is stored as string to a valid mysql date ,yyyy-mm-dd , in the order by for example

    SELECT *
from  table 
order by str_to_date(date_column,'%d.%m.%Y') desc

    You could be clearer on how your data is stored by providing samples

    Login or Signup to reply.
  2. 0

    Your third example uses %e. Changing that to %d will give you the format you want.

    If the value is stored in the database as a DATE, DATETIME or TIMESTAMP, then ordering by that value will give you what you need:
    SELECT DATE_FORMAT(date_column, '%d.%m.%Y') AS formatted_date FROM table ORDER BY date_column DESC

    N.B. Ordering by formatted_date won’t give the order you want, because it’s just a text string rather than a DATE or similar.

    If the value is not stored in the database as a DATE or similar, then the suggestion from P.Salmon is what you want.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search