我想将表中的记录按时间(updatedate)排序,但是如果时间一样的就按id排序,应该怎么写sql?我这样写但是排序还是错的,
select * from commend order by updatedate desc,id desc
执行后,Nov 4 2010 10:33AM竟然在Nov 4 2010 6:04PM之前了? 应该是PM的时间在AM时间的前面呀!commend 表
ID company_name updatedate
38 小蚂蚁1 Nov 4 2010 10:33AM
28 bb Nov 4 2010 10:32AM
21 2 Nov 4 2010 10:28AM
29 小蚂蚁2 Nov 4 2010 6:04PM
16 小蚂蚁3 Nov 4 2010 6:04PM
19 test Nov 3 2010 10:40AM
24 ddd Nov 1 2010 5:38PM
select * from commend order by updatedate desc,id desc
执行后,Nov 4 2010 10:33AM竟然在Nov 4 2010 6:04PM之前了? 应该是PM的时间在AM时间的前面呀!commend 表
ID company_name updatedate
38 小蚂蚁1 Nov 4 2010 10:33AM
28 bb Nov 4 2010 10:32AM
21 2 Nov 4 2010 10:28AM
29 小蚂蚁2 Nov 4 2010 6:04PM
16 小蚂蚁3 Nov 4 2010 6:04PM
19 test Nov 3 2010 10:40AM
24 ddd Nov 1 2010 5:38PM
如果是字符型的,通过charindex()来判断
select * from commend order by convert(varchar(20),updatedate,120) desc,id desc
--2
select * from commend order by replace(replace(updatedate,'AM',''),'PM','') desc,id desc