求一条SQL语句

Question

select * from tablename where right(dateandtime,5)='00:00'
或
select * from tablename where right(dateandtime,2)='00'
...

Answer 1

select * from tablename where right(convert(char(19),dateandtime,120),5)='00:00'
或
select * from tablename where right(convert(char(19),dateandtime,120),2)='00'
...

Answer 2

declare @interval int --间隔秒数set @interval=10
select * from T
where datediff(ss,(select top 1 dateandtime from T),dateandtime)=@interval

Answer 3

create table #t
(
dateandtime datetime not null,
item char(1) not null,
val numeric(19,6) not null
)
insert into #t values('2003-11-11 01:11:00','a',9.00)
insert into #t values('2003-11-11 01:11:01','a',10.00)
insert into #t values('2003-11-11 01:11:02','a',11.00)
insert into #t values('2003-11-11 01:11:03','a',12.00)
insert into #t values('2003-11-11 01:11:04','a',18.00)
godeclare @l_dt_start datetime --开始时间
declare @l_dt_end   datetime --截止时间
declare @l_interval int --间隔秒数select @l_dt_start='2003-11-11 01:11:00', @l_dt_end='2003-11-11 01:11:04', @l_interval=2select *
from #t
where dateandtime>=@l_dt_start and dateandtime<=@l_dt_end
and datediff(second, @l_dt_start, dateandtime)%@l_interval=0
--结果
dateandtime                                            item val                   
------------------------------------------------------ ---- --------------------- 
2003-11-11 01:11:00.000                                a    9.000000
2003-11-11 01:11:02.000                                a    11.000000
2003-11-11 01:11:04.000                                a    18.000000