让数据库产生一张详细的日历表
也许有了这张表，你的工作会轻松很多！
create table [dbo].[time_dimension] (
[time_id] [int] identity (1, 1) not null ,
[the_date] [datetime] null ,
[the_day] [nvarchar] (15) null ,
[the_month] [nvarchar] (15) null ,
[the_year] [smallint] null ,
[day_of_month] [smallint] null ,
[week_of_year] [smallint] null ,
[month_of_year] [smallint] null ,
[quarter] [nvarchar] (2) null ,
[fiscal_period] [nvarchar] (20) null
) on [primary]
declare @weekstring varchar(12),
@ddate smalldatetime,
@smonth varchar(20),
@iyear smallint,
@idayofmonth smallint,
@iweekofyear smallint,
@imonthofyear smallint,
@squarter varchar(2),
@ssql varchar(100),
@adddays int
select @adddays = 1 --日期增量（可以自由设定）
select @ddate = '01/01/2002' --开始日期
while @ddate < '12/31/2004' --结束日期
begin
select @weekstring = datename (dw, @ddate)
select @smonth=datename(mm,@ddate)
select @iyear= datename (yy, @ddate)
select @idayofmonth=datename (dd, @ddate)
select @iweekofyear= datename (week, @ddate)
select @imonthofyear=datepart(month, @ddate)
select @squarter = 'q' + cast(datename (quarter, @ddate)as varchar(1))
insert into time_dimension(the_date, the_day, the_month, the_year,
day_of_month,
week_of_year, month_of_year, quarter) values
(@ddate, @weekstring, @smonth, @iyear, @idayofmonth, @iweekofyear,
@imonthofyear, @squarter)
select @ddate = @ddate + @adddays
end
go
select * from time_dimension
