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

--（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