MySQL日期格式DD / MM / YYYY选择查询？
使用select格式化日期DD/MM/YYYY并按降序排列。语法如下-
SELECT *FROM yourTableName where yourDatetimeColumnName order by STR_TO_DATE(yourDatetimeColumnName,’%d/%m%Y’) desc;
上面的语法将按降序给出日期。为了理解上述语法，让我们首先创建一个表。创建表的查询如下-
mysql> create table DateFormatWithSelect -> ( -> UserId int, -> UserName varchar(100), -> UserLoginDatetime varchar(100) -> );
使用insert命令在表中插入一些记录。查询如下-
mysql> insert into DateFormatWithSelect values(101,'John','20/10/2016'); mysql> insert into DateFormatWithSelect values(102,'David','21/09/2015'); mysql> insert into DateFormatWithSelect values(103,'Carol','21/12/2018'); mysql> insert into DateFormatWithSelect values(104,'Mike','2/8/2014'); mysql> insert into DateFormatWithSelect values(105,'Sam','21/11/2017'); mysql> insert into DateFormatWithSelect values(106,'Bob','21/12/2013');
使用select命令显示表中的所有记录。查询如下-
mysql> select *from DateFormatWithSelect;
以下是输出-
+--------+----------+-------------------+ | UserId | UserName | UserLoginDatetime | +--------+----------+-------------------+ | 101 | John | 20/10/2016 | | 102 | David | 21/09/2015 | | 103 | Carol | 21/12/2018 | | 104 | Mike | 2/8/2014 | | 105 | Sam | 21/11/2017 | | 106 | Bob | 21/12/2013 | +--------+----------+-------------------+ 6 rows in set (0.00 sec)
这是将日期格式化为DD/MM/YYYY格式的SELECT-
mysql> select *from DateFormatWithSelect -> where UserLoginDatetime order by str_to_date(UserLoginDatetime,'%d/%m/%Y') desc;
输出结果
+--------+----------+-------------------+ | UserId | UserName | UserLoginDatetime | +--------+----------+-------------------+ | 103 | Carol | 21/12/2018 | | 105 | Sam | 21/11/2017 | | 101 | John | 20/10/2016 | | 102 | David | 21/09/2015 | | 104 | Mike | 2/8/2014 | | 106 | Bob | 21/12/2013 | +--------+----------+-------------------+ 6 rows in set, 6 warnings (0.00 sec)