MySQL MySQL日期格式DD/MM/YYYY选择查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10637581/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL date format DD/MM/YYYY select query?
提问by Karl Morrison
I'm a bit confused on how to order by date formats.
我对如何按日期格式排序有点困惑。
For the format YYYY-MM-DDyou would do this: ...ORDER BY date DESC...
对于格式，YYYY-MM-DD您将执行以下操作：...ORDER BY date DESC...
How would you order by DD/MM/YYYY?
你会如何订购DD/MM/YYYY？
This isn't working:
这不起作用：
SELECT * FROM $table ORDER BY DATE_FORMAT(Date, '%Y%m%d') DESC LIMIT 14
回答by trapper
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?
或者你真的想在前年前一个月前按天排序？
回答by eggyal
You can use STR_TO_DATE()to convert your strings to MySQL date values and ORDER BYthe result:
您可以使用STR_TO_DATE()将字符串转换为 MySQL 日期值和ORDER BY结果：
ORDER BY STR_TO_DATE(datestring, '%d/%m/%Y')
However, you would be wise to convert the column to the DATEdata type instead of using strings.
但是，明智的做法是将列转换为DATE数据类型而不是使用字符串。
回答by John Conde
SELECT DATE_FORMAT(somedate, "%d/%m/%Y") AS formatted_date
..........
ORDER BY formatted_date DESC
回答by Elton da Costa
Use:
用：
SELECT DATE_FORMAT(NAME_COLUMN, "%d/%l/%Y") AS 'NAME'
SELECT DATE_FORMAT(NAME_COLUMN, "%d/%l/%Y %H:%i:%s") AS 'NAME'
Reference: https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html
参考：https: //dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html
回答by Mani
SELECT DATE_FORMAT(COLUMN_NAME, "%d/%m/%Y %h:%i %p");
OR
或者
SELECT DATE_FORMAT("2019-05-10 19:30:10", "%d/%m/%Y %h:%i %p");
OUTPUT is 10/05/2019 07:30 PM
输出是10/05/2019 07:30 PM
回答by Davi RIbeiro
If the hour is important, I used str_to_date(date, '%d/%m/%Y %T' ), the %Tshows the hour in the format hh:mm:ss.
如果小时很重要，我使用str_to_date(date, '%d/%m/%Y %T' ), the%T显示小时的格式hh:mm:ss。
回答by Kishore Sahoo
for my case this worked
就我而言，这有效
str_to_date(date, '%e/%m/%Y' )
回答by xdazz
ORDER BY a date type does not depend on the date format, the date format is only for showing, in the database, they are same data.
ORDER BY 日期类型不依赖于日期格式，日期格式仅用于显示，在数据库中，它们是相同的数据。
