drop table qiqi1983731 create table qiqi1983731 (id int , [datetime] dateTime ,Content varchar(500)) insert into qiqi1983731 select '1','20090506','AAA' union all select '2','20090507','AAA' union all select '3','20090509','AAA' union all select '4','20090510','AAA' union all select '5','20090511','AAA' union all select '6','20090512','AAA' union all select '7','20090513','AAA' union all select '8','20090516','AAA' union all select '9','20090517','AAA' union all select '10','20090519','AAA' union all select '11','20090520','AAA' select top 7 id,convert(char(10),[datetime],126) as dTime, content from qiqi1983731 order by [dateTime] desc select A.dTime from ( select convert(char(10),dateadd(d,0,getdate()), 126)as dTime union all select convert(char(10),dateadd(d,-1,getdate()), 126) union all select convert(char(10),dateadd(d,-2,getdate()), 126) union all select convert(char(10),dateadd(d,-3,getdate()), 126) union all select convert(char(10),dateadd(d,-4,getdate()), 126) union all select convert(char(10),dateadd(d,-5,getdate()), 126) union all select convert(char(10),dateadd(d,-6,getdate()), 126) )A left join qiqi1983731 on A.dTime =convert(char(10),qiqi1983731.[datetime],126) where id is null ============================================ dTime 2009-05-18 2009-05-15 2009-05-14
create Procedure GetDates as begin declare @Day int,@Count int,@BeginDate datetime, @EndDatetime datetime,@InsertDate datetime set @Count=7 select @BeginDate=dateadd(day,-datepart(weekday,getdate())+2,getdate()) select @EndDatetime=dateadd(day,6,@BeginDate) declare @Table table(id int identity(1,1),Dates datetime) while(@Count>0) begin select @InsertDate=dateadd(day,@Count-1,@BeginDate) insert into @Table select @InsertDate select @Count=@Count-1 end select dates=convert(varchar(10),Dates,120) from @Table where convert(varchar(10),Dates,120) not in(select distinct datetimes=convert(varchar(10),datetimes,120) from GetDates ) end是不是這個樣子??
---这句是测试 你可以删除,试试! --select top 7 id,convert(char(10),[datetime],126) as dTime, --ntent from qiqi1983731 order by [dateTime] desc
这样就OK了。 IF OBJECT_ID('LI') IS NOT NULL drop table LI create table LI (id int , [datetime] dateTime ,Content varchar(500)) insert into LI select '8','20090516','AAA' union all select '9','20090517','AAA' union all select '10','20090519','AAA' union all select '11','20090520','AAA' UNION ALL select '5','20090521','AAA' union all select '6','20090522','AAA' union all select '7','20090523','AAA' declare @date smalldatetime SELECT @date = getdate()
--查询结果 SELECT LO.MARK FROM (SELECT convert(varchar,dateadd(dd,a.number,@date+2-datepart(dw,GETDATE())),112) AS MARK FROM master..spt_values a where type='p' and number<=6) LO where NOT EXISTS(SELECT 1 FROM LI WHERE convert(varchar,[datetime],112) = LO.MARK)
--结果
2009-05-20
drop table qiqi1983731
create table qiqi1983731 (id int , [datetime] dateTime ,Content varchar(500))
insert into qiqi1983731
select '1','20090506','AAA' union all
select '2','20090507','AAA' union all
select '3','20090509','AAA' union all
select '4','20090510','AAA' union all
select '5','20090511','AAA' union all
select '6','20090512','AAA' union all
select '7','20090513','AAA' union all
select '8','20090516','AAA' union all
select '9','20090517','AAA' union all
select '10','20090519','AAA' union all
select '11','20090520','AAA' select top 7 id,convert(char(10),[datetime],126) as dTime,
content from qiqi1983731 order by [dateTime] desc select A.dTime from (
select convert(char(10),dateadd(d,0,getdate()), 126)as dTime union all
select convert(char(10),dateadd(d,-1,getdate()), 126) union all
select convert(char(10),dateadd(d,-2,getdate()), 126) union all
select convert(char(10),dateadd(d,-3,getdate()), 126) union all
select convert(char(10),dateadd(d,-4,getdate()), 126) union all
select convert(char(10),dateadd(d,-5,getdate()), 126) union all
select convert(char(10),dateadd(d,-6,getdate()), 126)
)A left join qiqi1983731
on A.dTime =convert(char(10),qiqi1983731.[datetime],126)
where id is null
============================================
dTime
2009-05-18
2009-05-15
2009-05-14
create Procedure GetDates
as
begin
declare @Day int,@Count int,@BeginDate datetime, @EndDatetime datetime,@InsertDate datetime
set @Count=7
select @BeginDate=dateadd(day,-datepart(weekday,getdate())+2,getdate())
select @EndDatetime=dateadd(day,6,@BeginDate)
declare @Table table(id int identity(1,1),Dates datetime)
while(@Count>0)
begin
select @InsertDate=dateadd(day,@Count-1,@BeginDate)
insert into @Table select @InsertDate
select @Count=@Count-1
end
select dates=convert(varchar(10),Dates,120) from @Table
where convert(varchar(10),Dates,120) not in(select distinct datetimes=convert(varchar(10),datetimes,120) from GetDates )
end是不是這個樣子??
---这句是测试 你可以删除,试试!
--select top 7 id,convert(char(10),[datetime],126) as dTime,
--ntent from qiqi1983731 order by [dateTime] desc
IF OBJECT_ID('LI') IS NOT NULL
drop table LI
create table LI (id int , [datetime] dateTime ,Content varchar(500))
insert into LI select '8','20090516','AAA' union all
select '9','20090517','AAA' union all
select '10','20090519','AAA' union all
select '11','20090520','AAA' UNION ALL
select '5','20090521','AAA' union all
select '6','20090522','AAA' union all
select '7','20090523','AAA'
declare @date smalldatetime
SELECT @date = getdate()
--查询结果
SELECT LO.MARK
FROM
(SELECT convert(varchar,dateadd(dd,a.number,@date+2-datepart(dw,GETDATE())),112) AS MARK
FROM master..spt_values a where type='p' and number<=6) LO
where NOT EXISTS(SELECT 1 FROM LI WHERE convert(varchar,[datetime],112) = LO.MARK)
--删除测试数据
DROP TABLE LI
/*
MARK
20090518
20090524
*/