求助SQL

[bluesky1980]

表A有两个字段TIME(VARCHAR类型)NUM(INT类型)
TIME NUM
200803010000CST 10
200803011000CST 10
200803052000CST 10
200803053000CST 10
200803040000CST 10
200803031000CST 10
200803032000CST 10
200803043000CST 10

TIME的前八位代表插入该条数据时的日期
想写一个SQL语句,删除距今10天以外的数据,即删除掉
200803010000CST 10
200803011000CST 10
这两条数据

[dawugui]

delete from a where datediff(day , left(time,4) + '-' + substring(time,5,2) + '-' + substring(time,7,2) , getdate()) >= 10

[-狙击手-]

delete 
from a 
where datediff(day , left(time,4) + '-' + substring(time,5,2) + '-' + substring(time,7,2) , getdate()) > = 10

[dawugui]

create table A(TIME varchar(20) , NUM int)
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 )
go

delete from a where datediff(day , left(time,4) + '-' + substring(time,5,2) + '-' + substring(time,7,2) , getdate()) > 10
select * from A

drop table A

/*
TIME                 NUM         
-------------------- ----------- 
200803052000CST      10
200803053000CST      10
200803040000CST      10
200803031000CST      10
200803032000CST      10
200803043000CST      10

（所影响的行数为 6 行）
*/

[山之魂2]

不用那么麻烦的函数啊

delete from a where datediff(day , left(time,8) , getdate()) > 10

就可以了

[山之魂2]

不用那么麻烦的函数啊

delete from a where datediff(day , left(time,8) , getdate()) > 10

就可以了

[山之魂2]

不用那么麻烦的函数啊

delete from a where datediff(day , left(time,8) , getdate()) > 10

就可以了

[山之魂2]

不用那么麻烦的函数啊

delete from a where datediff(day , left(time,8) , getdate()) > 10

就可以了

[leisure_cool]

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()
*/