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

--（1） declare @StartTime Datetimeset @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',10union all select '01','2009-02',11union all select '01','2009-03',12union all select '02','2009-02',32union all select '02','2009-04',33select 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 1001 2009-02 1101 2009-03 1201 2009-04 001 2009-05 001 2009-06 002 2009-01 002 2009-02 3202 2009-03 002 2009-04 3302 2009-05 002 2009-06 0