您现在正在浏览：站长之家 >> 数据库 >> 详细内容

让数据库产生一张详细的日历表

时间:2007-12-23 来源:不详作者:迈克DB

也许有了这张表，你的工作会轻松很多！
CREATETABLE[dbo].[time_dimension](
[time_id][int]IDENTITY(1,1)NOTNULL,
[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@WeekStringvarchar(12),
@dDateSMALLDATETIME,
@sMonthvarchar(20),
@iYearsmallint,
@iDayOfMonthsmallint,
@iWeekOfYearsmallint,
@iMonthOfYearsmallint,
@sQuartervarchar(2),
@sSQLvarchar(100),
@adddaysint

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)asvarchar(1))
INSERTINTOtime_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*fromtime_dimension