根据某个日期得到该月的所有日期

--（1） declare @StartTime Datetime set @StartTime='2009-3-21' select convert(varchar(10),dateadd(month,datediff(month,0,@StartTime),0+number),120) '日期' from master..spt_values where type='P' and dateadd(day,number,dateadd(month,datediff(month,0,@StartTime),0))<dateadd(day,-day(@StartTime)+1,dateadd(mm,1,@StartTime)) --每一小时来划分一天 select right(100+number,2)+':00'+'-'+right(101+number,2)+':00' as '时间段' from master..spt_values where type='p' and number between 0 and 23 实例 declare @TT table ( ID char(10), DATE varchar(10), NUM int ) insert into @TT select '01','2009-01',10 union all select '01','2009-02',11 union all select '01','2009-03',12 union all select '02','2009-02',32 union all select '02','2009-04',33 select distinct T1.ID,T2.Date into #1 from ( select Date='2009-01' union all select Date='2009-02' union all select Date='2009-03' union all select Date='2009-04' union all select Date='2009-05' union all select Date='2009-06' ) T2,@TT T1 select T2.ID,T2.Date,isnull(T1.Num,0) '数量' from @TT T1 right join #1 T2 on T1.ID=T2.ID and T1.Date=T2.Date ID Date 数量 ---------- ------- ----------- 01 2009-01 10 01 2009-02 11 01 2009-03 12 01 2009-04 0 01 2009-05 0 01 2009-06 0 02 2009-01 0 02 2009-02 32 02 2009-03 0 02 2009-04 33 02 2009-05 0 02 2009-06 0