我想删除每天某一时间段的数据,如想删除17:30---08:00的加班数据,请问SQL如何写?偶是菜鸟,望赐教
数据库结构如下: ID FTIME DEVICENO
12232 2007-3-23 09:23:00 1
23334 2007-3-23 09:23:00 2
数据库结构如下: ID FTIME DEVICENO
12232 2007-3-23 09:23:00 1
23334 2007-3-23 09:23:00 2
--> 测试数据: @s
declare @s table (ID int,FTIME datetime,DEVICENO int)
insert into @s select 12232,'2007-3-23 09:23:00',1
union all select 23334,'2007-3-23 09:23:00',2
union all select 23334,'2007-3-23 17:23:00',2
union all select 23334,'2007-3-23 17:32:00',2
union all select 23334,'2007-3-23 07:23:00',2delete @s where
ftime between convert(varchar(10),ftime,120)+' 17:30:00' and convert(varchar(10),dateadd(dd,1,ftime),120)+' 08:00:00'
or
ftime between convert(varchar(10),dateadd(dd,-1,ftime),120)+' 17:30:00' and convert(varchar(10),ftime,120)+' 08:00:00'select * from @s
一样的:
delete from tab where ftime>='2008-5-1 0:0:0' and ftime<'2008-6-1 0:0:0'
where convert(char(8),ftime,108) > '17:30:00'
or convert(char(8),ftime,108) < '08:00:00'
delete from tab where ftime>='2008-5-1 0:0:0' and ftime<'2008-6-1 0:0:0' and
datepart(hh,ftime) * datepart(n,ftime) >= 17*60+30 and datepart(hh,ftime)<20