字段2为时间函数 放的数据有5:00 6:30 22:00 21:00 等等
但是用以下查询的结果是21:00
22:00
5:00
6:30sql="select * from 表1 where 字段1='"&daynow&"' order by 字段2 asc"看看出错到哪了想要的结果是5:00
6:30
21:00
22:00
但是用以下查询的结果是21:00
22:00
5:00
6:30sql="select * from 表1 where 字段1='"&daynow&"' order by 字段2 asc"看看出错到哪了想要的结果是5:00
6:30
21:00
22:00
insert #tb1 select '21:00'
insert #tb1 select '22:00'
insert #tb1 select '5:00'
insert #tb1 select '6:30'
select * from #tb1 order by cast('1900-1-1 '+dt as datetime) descdt
----------
22:00
21:00
6:30
5:00(4 行受影响)
create table tab(c1 varchar(10))
go
insert tab
select '21:00'
union select '22:00'
union select '5:00'
union select '6:30'
select * from tab order by cast(substring(c1,1,charindex(':',c1)-1) as int) ascdrop table tab
go
insert tab
select '21:00'
union select '22:00'
union select '5:00'
union select '6:30'
select *
from tab
order by right('00'+c1,5)
drop table tab
/*
c1
----------
5:00
6:30
21:00
22:00(4 個資料列受到影響)*/
insert #tb1 select '21:00'
insert #tb1 select '22:00'
insert #tb1 select '5:00'
insert #tb1 select '6:30'select * from #tb1 order by cast(dt as datetime)
/*dt
----------
5:00
6:30
21:00
22:00(4 行受影响)
*/
sql="select * from 表1 where 字段1='"&daynow&"' order by cast(字段2 as datetime) asc"
insert #tb1 select '21:00'
insert #tb1 select '22:00'
insert #tb1 select '5:00'
insert #tb1 select '6:30'select * from #tb1 order by cast( replace(dt,':','.') as float) asc