Guessing you probably just want to format the output date? then this is what you are after
SELECT *, DATE_FORMAT(date,'%d/%m/%Y') AS niceDate
FROM table
ORDER BY date DESC
LIMIT 0,14
Or do you actually want to sort by Day before Month before Year?
Answer from trapper on Stack OverflowMySQL date format DD/MM/YYYY select query? - Stack Overflow
CMV: MM/DD/YYYY Is almost always the best format for dates.
dates - DD/MM/YY or DD/MM/YYYY? - User Experience Stack Exchange
datetime - Are there locales or common programs that use YYYY-DD-MM as the date format? - Stack Overflow
Videos
Why YSK: YYYY-MM-DD is the International (ISO) Standard format for dates. This format makes the most sense for reasons of sorting and also eliminates any confusion due to different date formatting in different regions. This is especially useful when you have files in a folder that have dates in the name.
Guessing you probably just want to format the output date? then this is what you are after
SELECT *, DATE_FORMAT(date,'%d/%m/%Y') AS niceDate
FROM table
ORDER BY date DESC
LIMIT 0,14
Or do you actually want to sort by Day before Month before Year?
You can use STR_TO_DATE() to convert your strings to MySQL date values and ORDER BY the result:
ORDER BY STR_TO_DATE(datestring, '%d/%m/%Y')
However, you would be wise to convert the column to the DATE data type instead of using strings.
The world should get on the same system. I'm sure that the fact that the US (and maybe other countries) use MM/DD/YYYY and others use DD/MM/YYYY causes tons of inefficiencies and waste worldwide. So the question remains, what is the best way to standardize ourselves?
The US has a lot of things wrong, but in terms of date format, we're superior.
It is most efficient and logical for people to use MM/DD/YYYY because it most closely aligns with the way people think. The day is the most precise, important information, the year can in many cases be inferred or omitted, but the month is the most useful data point to receive/deliver FIRST.
When someone is going to be putting something into their calendar, digital or otherwise, what are they looking for first? The month is the primary piece of orienting information that the brain seeks when trying to locate a date. Even if the date in question is a future year, I'd bet most people advance forward through the months on a computer calendar rather than skip to January 2026.
MM/DD/YYYY most accurately reflects how we as humans receive and use time information, and as such should be the standard going forward.
The exception should be when naming computer files that should be organized in chronological order, in which case the logical naming convention should be YYYY/MM/DD.
Note that while MM/DD/YYYY is superior, MM/DD/YY is not. Any efficiency gained by the omission of 20XX is eclipsed by the potential confusion involved with not knowing which of the two optimal formattings are being expressed.
There is no universally good answer to this question, but there are definitely two pros of YYYY:
- by showing the two leading numbers you can easily tell e.g. 1911 from 2011,
- you know exactly where the year is in cases when the year is from the range XX01-XX12.
In other words:
Notation Possible interpretations:
----------- -------------------------
09/10/11 Four:
September 10th 2011
9th of October 2011
2009, 10th of November
2009, October 11th
09/10/2011 Two:
September 10th 2011
9th of October 2011
2009/10/11 Two as well:
2009, 10th of November
2009, October 11th
So, as you can see, by telling the User where the year is you limit the concern. Should the date be from a year which ends in a number higher than 31 (which is the highest number possible in the other fields), something interesting happens:
Notation Possible interpretations:
----------- -------------------------
09/10/33 Two:
September 10th 2033
9th of October 2033
However, the above interpretation requires the User to first analyse the contents of the string, so it increases the cognitive load significantly. The thinking would be:
"Is 09 the year? Not sure. The middle one is not year. Oh, 33 is the year. So 09 must be the day or month."
Of course this happens in a blink of an eye (Well, two of them. Well, three), but it is still a cognitive load and if Users need to deal with a lot of dates in this form, they may need to go through the same unwelcome process of searching for the year many times until they learn. And they should not have to learn.
And for these you do not need to bother of the contents, you can easily tell where the year is just looking at the obscured string:
- ▓▓-▓▓-▓▓▓▓
- ▓▓/▓▓/▓▓▓▓
- ▓▓▓▓/▓▓/▓▓
- ▓▓▓▓-▓▓-▓▓
- ▓▓▓▓-▓▓
- ▓▓-▓▓▓▓
The day vs month problem: gradual versus cultural approach
Now we get to the real culprit why the dates are so unclear: month versus day. Let us say, we have solved our problem with the year and still need to tell one from another here: ▓▓/▓▓/▓▓▓▓
Notation Possible interpretations:
----------- -------------------------
09/10/2033 Two:
September 10th 2033
9th of October 2033
For me, the gradual approach, where the time units consistently goes from lower to higher (so: DD/MM/YYYY) or the other way (YYYY/MM/DD) makes much more sense. Unfortunately, in a system that Users only approach from time to time it does not matter if you use this approach, because they will not remember that you have used it.
On the other hand, the MM/DD/YYYY format for the date is common in the US, Canada, Greenland, Philippines and several African countries (source), as an abbreviation of the way the date is pronounced: "September 10th, 2017". However, as there is also another pronunciation allowed this brings only confusion.
To get out of this madness you may consider changing MM to the textual version of it (e.g. shortened: OCT/10/2017 or 09/SEP/2017), but in this case you fall into a problem of translation for international Users.
Professional usage
One situation when you do not need to bother about the notation is a situation when Users deal a lot with the date data, mostly in professional way. Two examples I can give you out of my head would be financial analysts (observing changes on the market) or photographers dealing with a lot of photos named using some convention they know by heart. If they know it by heart, this is not a concern.
"Now" context anchor
Another situation when the importance of what is year in the date becomes less important is when Users are more oriented on "now". Facebook is a good example.
Saving space
Saving space may be sometimes a really important factor for making decisions. Again, in dashboards containing a lot of data, the year may be either completely obsolete or may need to be truncated. But I believe these dashboards fall into the basket of professional usage most of the times, so no need to worry about them too much.
Combining into one text string and sorting
In some cases, you may face a situation when you need to combine the date into one big chunk of text. For example, the naming convention I use for photo files is YYYYMMDD_HHmmSS.ext, (e.g. 20170911_113426.RAF) This, again, falls into the "pro" usage basket; however it also provides means for sorting by date without needing to worry that the date attribute of a file would change (e.g. because it was moved to a file system that does not support this kind of attribute, or edited in an app that would clear it). This usage scenario brings two conclusions:
- it is good to have a full year, because at least the photos from 2000 will be after those from 1999,
- it is good to use the gradual order, progressing from higher to lower unit.
Wrap-up:
Do your Users know the system by heart? Do they use the date attribute on everyday basis? If so, do not bother about the recognition where the year is, but consider additional things like sortability or uniqueness (e.g. 1911 from 2011 when the date scope is wide.)
Do your Users approach the date attribute only occasionally? If so, provide higher recognition for what is what in the date without high cognitive load from their side: expand the year to four digits, make it clear where the month is. Unless space is critical for you, in which case you need to prepare for trade-offs.
EDIT:
As many comments below refer to ISO-8601 standard, I would like to explain why I have not added it in my original answer.
I believe that the word "standard" has a twofold meaning: a norm and a convention.
A a convention is a common approach to something that is used by a limited group of people. Regarding a specific topic like this one, there can be (and there usually are) various conventions, out of which one can contradict another (again: like in this case). And what is more, most of the conventions contradict the norm and the norm contradicts most of the conventions.
Conventions have their historical, linguistic, practical etc. roots. In case of date conventions, for example the MM/DD/YY comes from American way of saying the date as "November 5th, 2008" whereas somewhere else it can be different.
Now to the norm.
A norm has a role to deny most of the conventions used so far, to replace most of them. It can be one of the conventions that has been selected as a norm, but most of the conventions need to be denied if just one has to stay. A norm usually is well thought out. The norm in this particular case makes a lot of sense, as every-next-unit in it is smaller than the previous one (and this allows easier comparison between dates, sorting by date as a text string etc.).
There are definitely two ways to go from here.
One is to push the norm until it is used everywhere, in long term providing coherence in the standard used all over the world. Forcing people to use something different from what they have always used has got its drawbacks, and this way is - to some extent - against usability.
The other option is to adapt to the local conventions people understand. Having derived from cultural, linguistic, practical reasons, the conventions feel locally more adequate. But at the same time, when people using some convention also meet the other ones while browsing the web may become confused when they see something different from what they got used to, and hailing this approach is also - to some extent - against usability.
This way, I still believe that there is no universally good answer to this question, and it may not appear any soon. What can be done for now is limiting some bits of confusion - like in case of the year being written as four, not two digits.
As a rule, it's never OK to use a 2-digit year. If you can prove that using a 4-digit year will cause thousands of babies and cute fluffy bunnies to die horribly, that could be an exception to the rule, but probably not.
I have seen hundreds of costly process failures simply because a programmer thought it was perfectly OK to use a 2-digit year or a local date idiom this time.
But I have never once seen a program or process fail due to the choice of YYYY-MM-DD ISO 8601 Standard Date Formatting. It's always the right choice. Take this advice to heart, and you'll never regret it.
The YYYY-MM-DD standard form is equally comprehensible to people from any country, of any age - even ones that were born in countries where the traditional format is something objectively braindead, like MM/DD/YY or DD/YY-MM. It also sorts correctly without reformatting, a bonus feature!
See "Calendar date" on Wikipedia on the topic - it lists the countries by date/time format.
At first glance it doesn't look like anyone is using YYYY-DD-MM regularly.
yyyy-mm-dd in particular is a subset of the ISO-8601 format.
There is no recognised standard that uses year date month. When year is first, it should always be followed by an appropriate sub-year, be that quarter, month, julian day, etc.
Some advantages to using year-first is that the order also happens to work well for alpha-sorting when used as a text value (such as part of a file name).
Hello how are you?
I`m Marcelo de C., an independent Advisor. It is a pleasure to help you today. I know how frustrating it can be for you, but I will do my best to help you :)
Go to settings>time and language>add clocks for different time zones.
Select the date and time tab.
Change date and time>change calendar settings.
In the format look for English(United States) and select.
In abbreviated date and time Choose the option MM/dd/yyyy.
Click apply and see if it has changed.
I hope the information above helps. Please let me know how it goes.
Have a nice day and stay safe.
Marcelo de C.
Since this is an older message you may have discovered the answer already, but just in case, or if somebody stumbles on this like I did....
Had the same issue on a new laptop.
tracked down "regions" in the settings.
set region to English (United States).
after applying the proper date options are available.
hope this helps somebody.