求个简单的sql语句

Question

脑子有转不过来了
有个date列，查询昨晚9点到今早10的所有数据

Answer 1

select * from tb 
where (datediff(day , dt , getdate()) = 1 and convert(varchar(8),dt,114) >= '21:00:00') or
(datediff(day , dt , getdate()) = 0 and convert(varchar(8),dt,114) <= '10:00:00')

Answer 2

select 
    *
from 
    your_table
where 
    [date] between '2010-04-02 21:00:00.000' and '2010-04-03 10:00:00.000'

Answer 3

select
  *
from
  tb
where
  [date]
between 
  convert(varchar(10),dateadd(dd,-1,getdate()),120)+'21:00'
and
  convert(varchar(10),getdate(),120)+'10:00'

Answer 4

create table tb(dt datetime)
insert into tb values('2010-04-02 22:00:00')
insert into tb values('2010-04-02 12:00:00')
insert into tb values('2010-04-03 09:00:00')
insert into tb values('2010-04-03 12:00:00')
goselect * from tb 
where (datediff(day , dt , getdate()) = 1 and convert(varchar(8),dt,114) >= '21:00:00') or
(datediff(day , dt , getdate()) = 0 and convert(varchar(8),dt,114) <= '10:00:00')drop table tb/*
dt                                                     
------------------------------------------------------ 
2010-04-02 22:00:00.000
2010-04-03 09:00:00.000（所影响的行数为 2 行）
*/

Answer 5

把我上面的列dt-->date
create table tb(date datetime)
insert into tb values('2010-04-02 22:00:00')
insert into tb values('2010-04-02 12:00:00')
insert into tb values('2010-04-03 09:00:00')
insert into tb values('2010-04-03 12:00:00')
goselect * from tb 
where (datediff(day , date , getdate()) = 1 and convert(varchar(8),date,114) >= '21:00:00') or
(datediff(day , date , getdate()) = 0 and convert(varchar(8),date,114) <= '10:00:00')drop table tb/*
dt                                                     
------------------------------------------------------ 
2010-04-02 22:00:00.000
2010-04-03 09:00:00.000（所影响的行数为 2 行）
*/

Answer 6

where convert(char(19),[date],120) between '2010-04-02 21:00:00' and '2010-04-03 10:00:00'

Answer 7

select *
from tb
where datepart(hh,dateadd(hh,3,[date])) between 0 and 13