日期:2014-05-18 浏览次数:20515 次
create table #date(name varchar(50),[date] datetime) insert into #date select 'aa','2009-01-01' union select 'aa','2009-02-02' union select 'aa', '2009-03-03' union select 'aa','2009-07-04' union select 'aa','2009-06-08' select * from #date declare @startdate varchar(30) declare @enddate varchar(30) set @startdate = '2009-09' set @enddate = '2009-11' --求出所有的时间段,这个稍微复杂点 select convert(varchar(4),a.number)+'-'+right('0'+convert(varchar(2),b.number),2) as year_mon into #temp from master..spt_values a ,master..spt_values b where a.type='p' and b.type='p' and b.number<=12 and b.number>0 and a.number>='1900' and a.number<'2048' and cast(convert(varchar(4),a.number)+'/'+convert(varchar(2),b.number)+'/01' as datetime)>=@startdate+'-01' and cast(convert(varchar(4),a.number)+'/'+convert(varchar(2),b.number)+'/01' as datetime)<=@enddate+'-01' select 'aa' name,* from #temp where year_mon not in (select CONVERT(varchar(7),[date],120) from #date where ISNULL([date],'')<>'')
------解决方案--------------------
/*
name time
aa 2009-01-01
aa 2009-02-02
aa 2009-03-03
aa 2009-07-04
aa 2009-06-08
查询出
name time
aa 2009-04-01
aa 2009-05-02
*/
go
if OBJECT_ID('tbl')is not null
drop table tbl
go
create table tbl(
[name] varchar(2),
[time] date
)
go
insert tbl
select 'aa','2009-01-01' union all
select 'aa','2009-02-02' union all
select 'aa','2009-03-03' union all
select 'aa','2009-07-04' union all
select 'aa','2009-06-08'
select c.[time],isnull(c.name,'aa') as name from
(select
isnull([time],'2010-'+right('00'+ltrim(number),2)) as [time],--实现按月份递增
a.name
from master..spt_values b
left join
(
select convert(varchar(7),[time],120) as [time] ,name from tbl
) a
on b.number=month(a.[time]+'-01')
where b.type='p' and b.number between 1 and 7
)c
where c.[time] not in (select convert(varchar(7),[time],120) as [time] from tbl)
/*
time name
2010-04 aa
2010-05 aa
*/
不知道你月的后面的日期想怎么处理
这个能看懂吧