SQL ORDER BY 日期问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1545888/
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
SQL ORDER BY date problem
提问by dnyaneshwar
Can you please help me in solving this problem. I am trying to order the results of an SQL query by date, but I'm not getting the results I need.
你能帮我解决这个问题吗？我正在尝试按日期对 SQL 查询的结果进行排序，但没有得到我需要的结果。
The query I'm using is:
我正在使用的查询是：
SELECT date FROM tbemp ORDER BY date ASC
Results are:
结果是：
01/02/2009
03/01/2009
04/06/2009
05/03/2009
06/12/2008
07/02/2009
Results should be:
结果应该是：
06/12/2008
03/01/2009
01/02/2009
07/02/2009
I need to select the date in the format above.
我需要以上述格式选择日期。
Your help is much appreciated.
非常感谢您的帮助。
回答by manji
回答by Jon Skeet
It sounds to me like your column isn't a date column but a text column (varchar/nvarchar etc). You should store it in the database as a date, not a string.
在我看来，您的列不是日期列而是文本列（varchar/nvarchar 等）。您应该将其作为日期而不是字符串存储在数据库中。
If you haveto store it as a string for some reason, store it in a sortable format e.g. yyyy/MM/dd.
如果出于某种原因必须将其存储为字符串，请将其存储为可排序的格式，例如 yyyy/MM/dd。
As najmeddine shows, you couldconvert the column on every access, but I would try very hard not to do that. It will make the database do a lotmore work - it won't be able to keep appropriate indexes etc. Whenever possible, store the data in a type appropriate to the data itself.
正如najmeddine 所示，您可以在每次访问时转换列，但我会非常努力地不这样做。这将使数据库做了很多更多的工作-它不会能够保持适当的索引等。只要有可能，将数据存储在一个类型适合于数据本身。
回答by Michael A
Unsure what dbms you're using however I'd do it this way in Microsoft SQL:
不确定您使用的是什么 dbms，但是我会在 Microsoft SQL 中这样做：
select [date]
from tbemp
order by cast([date] as datetime) asc
回答by ghiboz
this works for me:
这对我有用：
SELECT datefield
FROM myTable
ORDER BY CONVERT(DATE, datefield) ASC
SELECT datefield
FROM myTable
ORDER BY CONVERT(DATE, datefield) ASC
回答by khushmeet singh
Try using this this work for me
尝试为我使用这项工作
select * from `table_name` ORDER BY STR_TO_DATE(start_date,"%d-%m-%Y") ASC
where start_dateis the field name
其中start_date是字段名称
回答by DiggDev
SELECT CONVERT(char(19), CAST(date AS datetime), 101) as [date]
FROM tbemp
ORDER BY convert(datetime, date, 101) ASC
SELECT CONVERT(char(19), CAST(date AS datetime), 101) as [date]
FROM tbemp ORDER BY convert(datetime, date, 101) ASC
回答by Bhushan Sapre
Following answer may help you
下面的回答可能对你有帮助
perform your date ordering by your date identifier but use to_char() function in select clause and use some other identifier in select clause for date
按日期标识符执行日期排序，但在 select 子句中使用 to_char() 函数并在日期的 select 子句中使用其他标识符
e.g.
例如
SELECT TO_CHAR(DISPDATE1,'DD/MM/YYYY') AS DISPDATE,
SUM(APPLCOUNT) AS APPLIED,
SUM(CONFCOUNT) AS CONFIRMED
FROM
(
SELECT COUNT(ID) AS APPLCOUNT,
0 AS CONFCOUNT,
STUDENT.APPLIED_ON AS DISPDATE1
FROM STUDENT
WHERE STUDENT.ID = P_ID
GROUP BY STUDENT.APPLIED_ON
UNION
SELECT 0 AS APPLCOUNT,
COUNT(ID) AS CONFCOUNT,
STUDENT.CONFIRMED_ON AS DISPDATE1
FROM STUDENT
WHERE STUDENT.ID = P_ID
GROUP BY STUDENT.CONFIRMED_ON
)
GROUP BY DISPDATE1
ORDER BY DISPDATE1;
回答by user3564654
I wanted to edit several events in descendant chonologic order, and I just made a :
我想按后代 chonologic 顺序编辑几个事件，我只是做了一个：
select
TO_CHAR(startdate,'YYYYMMDD') dateorder,
TO_CHAR(startdate,'DD/MM/YYYY') startdate,
...
from ...
...
order by dateorder desc
and it works for me. But surely not adapted for every case... Just hope it'll help someone !
它对我有用。但肯定不是每一种情况都适用......只是希望它会帮助某人！
回答by Jayaprakasha
This may help you in mysql, php.
这可能对您在 mysql、php 中有所帮助。
//your date in any format
$date = $this->input->post('txtCouponExpiry');
$day = (int)substr($date, 3, 2);
$month = (int)substr($date, 0, 2);
$year = (int)substr($date, 7, 4);
$unixTimestamp = mktime(0, 0, 0, $year, $day, $month);
// insert it into database
'date'->$unixTimestamp;
//query for selecting order by date ASC or DESC
select * from table order_by date asc;
回答by Zainul Abid
try this
尝试这个
Order by Convert(datetime,@date) desc
