如何按日期对数据库进行排序 [英] How to sort a database in respect to date

问题描述

 选择 顶部  10  * 来自新闻_事件订单  by  NewsDate; 

我已经尝试了上面的查询；但输出是
2011年1月10日12:00:00上午
1/11/2011 12:00:00 AM
2/10/2011 12:00:00 AM
2/11/2011 12:00:00 AM
2011/3/10上午12:00:00
2011/3/11上午12:00:00

NewsDate数据类型为smalldatetime.

任何人都可以建议我一些解决方法.

在此先感谢您.

解决方案

检查您的日期列是datetime而不是varchar，您的排序将比排序字符串正确且更快.

同样，日期视图格式可以是mm/dd/yyyy，而不是RaisKazi所说的dd/mm/yyyy.

如果您确定字段的类型为smalldatetime ，则输出肯定是正确的，因此可以说服自己正确地运行此SQL，该SQL将显示月份名称，并且您将了解问题可能出在查找这些数字的方式上:

 选择 *，转换( varchar ，新闻日期)，
             来自 News_Events 订单  by  NewsDate;  pre> 

尝试一下

 选择 顶部  10  * 来自新闻_事件订单  by  转换(日期时间，NewsDate)； 

Select Top 10 * from News_Events order by NewsDate;

I have tried the above query; but the output is
1/10/2011 12:00:00 AM
1/11/2011 12:00:00 AM
2/10/2011 12:00:00 AM
2/11/2011 12:00:00 AM
3/10/2011 12:00:00 AM
3/11/2011 12:00:00 AM

NewsDate data-type is smalldatetime.

Anyone please suggest me some way to solve this.

Thanks in advance.

解决方案

Check your date column is datetime instead of a varchar and your sorting will be done correctly and faster than sorting strings.

Also the date view format could be mm/dd/yyyy and not dd/mm/yyyy like RaisKazi said.

If you are sure that the type of the field is smalldatetime so the output is surely correct so to convince yourself that its correct try running this SQL which will show you the month names and you will understand that the problem is, probably, in the way you are looking to those numbers :

Select  * , CONVERT(varchar, newsdate),
             from News_Events order by NewsDate;

Try this

Select Top 10 * from News_Events order by convert(datetime,NewsDate);

这篇关于如何按日期对数据库进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！