表A有两个字段TIME(VARCHAR类型)NUM(INT类型)
TIME NUM
200803010000CST 10
200803011000CST 10
200803052000CST 10
200803053000CST 10
200803040000CST 10
200803031000CST 10
200803032000CST 10
200803043000CST 10TIME的前八位代表插入该条数据时的日期
想写一个SQL语句,删除距今10天以外的数据,即删除掉
200803010000CST 10
200803011000CST 10
这两条数据
TIME NUM
200803010000CST 10
200803011000CST 10
200803052000CST 10
200803053000CST 10
200803040000CST 10
200803031000CST 10
200803032000CST 10
200803043000CST 10TIME的前八位代表插入该条数据时的日期
想写一个SQL语句,删除距今10天以外的数据,即删除掉
200803010000CST 10
200803011000CST 10
这两条数据
from a
where datediff(day , left(time,4) + '-' + substring(time,5,2) + '-' + substring(time,7,2) , getdate()) > = 10
insert into A values('200803010000CST', 10 )
insert into A values('200803011000CST', 10 )
insert into A values('200803052000CST', 10 )
insert into A values('200803053000CST', 10 )
insert into A values('200803040000CST', 10 )
insert into A values('200803031000CST', 10 )
insert into A values('200803032000CST', 10 )
insert into A values('200803043000CST', 10 )
godelete from a where datediff(day , left(time,4) + '-' + substring(time,5,2) + '-' + substring(time,7,2) , getdate()) > 10
select * from Adrop table A/*
TIME NUM
-------------------- -----------
200803052000CST 10
200803053000CST 10
200803040000CST 10
200803031000CST 10
200803032000CST 10
200803043000CST 10(所影响的行数为 6 行)
*/
declare @table table (Time varchar(20),Num int)
insert into @table
select '200803010000CST',10
union all
select '200803011000CST',10
union all
select '200803052000CST',10
union all
select '200803053000CST',10
union all
select '200803040000CST',10
union all
select '200803031000CST',10
union all
select '200803032000CST',10
union all
select '200803043000CST',10 delete from @table
where datediff(day,convert(datetime,left(Time,8)),convert(datetime,'2008-03-13'))>10
/*
Time Num
---------------------
200803010000CST 10
200803011000CST 10
*/
/*
备注,用convert(datetime,'2008-03-13'),是因为您是昨天发的贴,
如果是今天发的贴则用getdate()
*/