生成时间维

先建立一个dimdate的structure五木有古弟母而致皆，继时先贬利孝高道然后直接用procedure生成数据南顺中古聪悔仕南。非乱子断阉确定起始日期和结束日期五木有古弟母而致皆，继时先贬利孝高道然后就插入记录了南顺中古聪悔仕南。非乱子断阉

下面是基本的代码五木有古弟母而致皆，继时先贬利孝高道你可以再加入你自己需要的function

declare
@start_date datetime
,@end_date datetime
,@loop_day datetime
,@diff int
,@loop int

Select
@start_date ='1996-01-01'
,@end_date = '1998-12-31'
,@loop = 0

select
@diff = datediff(dd,@start_date,@end_date)

while @loop <= @diff
begin
        select @loop_day = dateadd(dd,@loop,@start_date)
        insert into dimDate
        select
                convert(varchar,@loop_day,112),
                @loop_day,
                dayOfWeek=case datepart(weekday,@loop_day)
                        when 1 then 7
                        else datepart(weekday,@loop_day)-1
                        end,
                DATENAME(WEEKDAY, @loop_day),
                day(@loop_day),
                1+datediff(dd,dateadd(yy,datediff(yy,0,@loop_day),0),@loop_day),
                DATEPART("week",@loop_day),
                month(@loop_day),
                DATENAME(month, @loop_day),
                year(@loop_day),
                'Q'+cast (datepart(qq,@loop_day) as char),
                isweekend=case datepart(weekday,@loop_day)
                        when 1 then 'yes'
                        when 6 then 'yes'
                        when 7 then 'yes'
                        else 'no'
                end

         select @loop = @loop + 1
end

*******************************

create   table   table1(the_date   datetime)  
  declare   @i   int  
  select   @i=datediff(day,'2001-01-01',getdate())  
  while   (@i>0)    
  begin  
  insert   into   table1   (the_date)   select   dateadd(day,@i,'2001-01-01')  
  select   @i=@i-1  
  end

********************************

declare   @b   datetime  
  set   @b   =   '2001-1-1'  
  while   @b   <=   getdate()  
  begin  
      insert   into   yourtable   (the_date,   the_day,   the_month)   values  
          (@b,   day(@b),   month(@b))  
      set   @b   =   dateadd(day,   @b,   1)  
  end  
  select   *   from   yourtable

*****************

declare   @b   datetime  
  set   @b   =   '2001-1-1'  
  while   @b   <=   getdate()  
  begin  
      insert   into   yourtable   (the_date,   the_day,   the_month)   values  
          (@b,   day(@b),   month(@b))  
      set   @b   =   dateadd(day,   1,   @b)  
  end  
  select   *   from   yourtable  

*******************
1，建序数表  
  select   top   8000   identity(int,0,1)   as   N   into   numtab   from    
  (select   top   100   id=1     from   sysobjects)   as   a,  
  (select   top   100   id=1     from   sysobjects)   as   b,  
  (select   top   100   id=1     from   sysobjects)   as   c  
   
  2,  
  insert   into   表(the_date,the_day,the_month)  
  select   n+cast('2001-01-01'   as   datetime)  
        ,day(n+cast('2001-01-01'   as   datetime))  
        ,month(n+cast('2001-01-01'   as   datetime))  
  from   Numtab  
  where     n+cast('2001-01-01'   as   datetime)>getdate()

********************

创建临时表

select top 55152 identity(int,1,1) as iid into #tmp
from sysobjects a,sysobjects b,sysobjects c
select * from #tmp

生成时间维语句
select *,dateadd(day,iid-1,'1949-01-01') as 日期,
year(dateadd(day,iid-1,'1949-01-01')) as 年份,
month(dateadd(day,iid-1,'1949-01-01')) as 月份,
day(dateadd(day,iid-1,'1949-01-01')) as 日期,
datepart(quarter,(dateadd(day,iid-1,'1949-01-01'))) as 季度,
datepart(weekday,(dateadd(day,iid-1,'1949-01-01'))) as 星期,
day(dateadd(day,iid-1,'1949-01-01')) as 月的第几表,
datepart(week,(dateadd(day,iid-1,'1949-01-01'))) as 年的第几周
from #tmp