Doesn't sort the date correctly?

Question

MySQL

The table stores for each record the full date in timestamp format.
When I select the field from_unixtime(mydate, '%m.%d.%Y') as dates
AND I do an order by dates, it sorts correctly, but if you set the format to '%d.%m.%Y', then it sorts the data incorrectly . Why?

Answer 1

Because you sort not dates, but their string representations.
In the first option - first by month, then by day, then by year. So December 31, 2000 will be later than January 01, 2017.
In the second - by day, then month, then year. And the 01st will be earlier than the 31st regardless of the month and year.
What's stopping you from sorting directly by mydate?