MySQL日期格式DD/MM/YYYY选择查询?

I'm a bit confused on how to order by date formats.

我对如何按日期格式排序有点困惑。

For the format YYYY-MM-DD you would do this: ...ORDER BY date DESC...

对于YYYY-MM-DD格式，您可以这样做:……按日期顺序DESC……

How would you order by DD/MM/YYYY?

你们是怎么订的?

This isn't working:

这不是工作:

SELECT * FROM $table ORDER BY DATE_FORMAT(Date, '%Y%m%d') DESC LIMIT 14

9 个解决方案

#1

You can use STR_TO_DATE() to convert your strings to MySQL date values and ORDER BY the result:

您可以使用STR_TO_DATE()将您的字符串转换为MySQL日期值和顺序，结果如下:

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.

但是，您最好将列转换为日期数据类型，而不是使用字符串。

#2

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?

或者你真的想要逐日逐月进行排序吗?

#3

SELECT DATE_FORMAT(somedate, "%d/%m/%Y") AS formatted_date
..........
ORDER BY formatted_date DESC

#4

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

#5

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?

或者你真的想要逐日逐月进行排序吗?

#6

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.

按日期类型排序不依赖于日期格式，日期格式仅用于显示，在数据库中，它们是相同的数据。

#7

for my case this worked

对我来说，这是可行的

str_to_date(date, '%e/%m/%Y' )

#8

If the hour is important, I used str_to_date(date, '%d/%m/%Y %T' ), the %T shows the hour in the format hh:mm:ss.

如果小时很重要，我使用了str_to_date(日期，'%d/%m/%Y %T')， %T以hh:mm:ss的格式显示小时。

#9

Format date in mysql using code igniter

使用代码点火器在mysql中格式化日期

        $from=$_POST['from'];
        $to=$_POST['to'];
        $table='Your table';
        $alias="count(userid)";
        $groupby="date_format(yourdate,('%Y-%m'))";
        $orderby=array('mostdrill'=>'DESC');
        $where="yourdate between '".date('Y-m-d',strtotime($from))."' and '".date('Y-m-d',strtotime($to))."'";
        $userdata=$this->yourmodel->function name($alias,$table,$where,'',$orderby,"",$groupby);

Hope it's helpful to know more click here

希望了解更多，点击这里