--也許有了這張表，你的工作會輕松很多！
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
---補個小日歷
create table a(dd datetime,groups int)
declare @count int,@i int,@starttime datetime
set @starttime='2003-01-01'
set @count=365
set @i=0
while @count>0
begin
insert a
select @starttime,datename(week,@starttime)
set @starttime=@starttime+1
set @count=@count-1
set @i=@i+1
end
-----月歷:
create proc years
@month int
AS
select max(case datepart(dw,dd) when 2 then cast(day(dd)as varchar(2)) else '' end) as '星期一',
max(case datepart(dw,dd) when 3 then cast(day(dd)as varchar(2)) else '' end) as '星期二',
max(case datepart(dw,dd) when 4 then cast(day(dd)as varchar(2)) else '' end) as '星期三',
max(case datepart(dw,dd) when 5 then cast(day(dd)as varchar(2)) else '' end) as '星期四',
max(case datepart(dw,dd) when 6 then cast(day(dd)as varchar(2)) else '' end) as '星期五',
max(case datepart(dw,dd) when 7 then cast(day(dd)as varchar(2)) else '' end) as '星期六',
max(case datepart(dw,dd) when 1 then cast(day(dd)as varchar(2)) else '' end) as '星期日'
from a where datepart(MM,dd)=@month group by groups
go
exec years 10 --十月份的日歷！
----------------
星期一 星期二 星期三 星期四 星期五 星期六 星期日
---- ---- ---- ---- ---- ---- ----
1 2 3 4 5
6 7 8 9 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30 31
