T-SQL创建时间维
use POCgoIF OBJECT_ID('dbo.isLeapYear','function') IS NOT NULLDROP function dbo.isLeapYearIF OBJECT_ID('dbo.TD_CALENDAR','TABLE') IS NOT NULLDROP TABLE dbo.TD_CALENDARgo--创建维表--@date_int,@date_v,@date_name,@year_int,@year_name,@quarter_int,@quarter_name,@month_int,@month_name,nullCREATE TABLE TD_CALENDAR(DATE_ID int NOT NULL,DATE_VALUE DATE,DATE_NAME VARCHAR(16),YEAR_ID int,YEAR_NAME VARCHAR(16),QUARTER_ID int,QUARTER_NAME VARCHAR(16),MONTH_ID int,MONTH_NAME VARCHAR(16),WEEK_ID INT,WEEK_NAME VARCHAR(20));go--创建函数isLeapYear 判断是否为闰年，输入参数为INT型的年份--返回1则表示是r闰年create function isLeapYear (@year int)returns intASbegindeclare @isLeap int = 0;if (datediff(day,cast(@year as char)+ '-1-1 ',cast(@year+1 as char)+ '-1-1 ')) = 366set @isLeap = 1else set @isLeap = 0 return @isLeapendgodeclare @date_v date = '2000-12-31';--设置起始年份,实际从下一年开始declare @yearCount int=1--@yearCount 年数的循环计数器declare @numbersOfYear int = 10 ;--@yearCount 设置创建维表的年数while @yearCount <= @numbersOfYearbegindeclare @i int = 0;--循环计数器declare @iCount int;--平年@iCount = 364,闰年@iCount = 365declare @date_int int;--int型的date key 如20010101declare @date_name varchar(20);declare @year_int int;declare @year_name varchar(16);declare @quarter_int int;declare @quarter_name varchar(16);declare @month_int int;declare @month_name varchar(16);declare @week_int int;declare @week_name varchar(20);if dbo.isLeapYear(YEAR(DATEADD(DD,1,@date_v))) =1 --isLeapYear函数判断该年是否为闰年set @iCount = 365else set @iCount = 364while @i <= @iCountbegin--从初始化值的下一年的第一天开始,本应为DO WHILE循环的set @date_v = DATEADD(DD,1,@date_v);--INT型的DATE KEY 用下式返回并不理想 会产生如 2011019 缺0的情况,故应改用DATEPART函数-- set @date_int = CONVERT(INT,DATENAME(YYYY,@date_v) + DATENAME(MM,@date_v) + DATENAME (DD,@date_v));set @date_int = DATEPART (YEAR,@date_v) * 10000 + DATEPART (MONTH,@date_v)*100 +DATEPART (DAY,@date_v)set @date_name = DATENAME(YYYY,@date_v) + '年' + DATENAME(MM,@date_v) + '月' + DATENAME (DD,@date_v) + '日';set @year_int = DATEPART(YYYY,@date_v);set @year_name = DATENAME(YYYY,@date_v)+ '年';set @quarter_int = DATEPART(QUARTER,@date_v);set @quarter_name = '第' + DATENAME(QUARTER,@date_v) + '季度';set @month_int = DATEPART (MONTH,@date_v);set @month_name = DATENAME(MONTH,@date_v) + '月';set @week_int = DATEPART(WEEK ,@date_v)set @week_name = DATENAME(WEEK,@date_v) + '周' insert into POC.dbo.TD_CALENDARVALUES(@date_int,@date_v,@date_name,@year_int,@year_name,@quarter_int,@quarter_name,@month_int,@month_name,@week_int,@week_name);set @i = @i + 1;end set @yearCount = @yearCount + 1;endgo--select COUNT(*) from POC.dbo.td_calendar--where year_id = 2004--truncate table poc.dbo.td_calendar?
用到的日期函数整理一下:
DATEADD?(@Period,@int,@smalldatetime ) 返回smalldatetime
@Period为区间类型(Such as Year or Month )
作用:返回增减后的日期
?
DATEPART(@Period,@datetimeoffset) 返回 int
DATETIMEOFFSET数据类型，此数据类型让你存储的日期和时间（24小时制）是时区一致的
作用:取得需要日期部分的整型
?
DATENAME(@Period,@datetimeoffset)返回 nvarchar
作用:取得需要日期部分的字符串型
-- 但是实验结果是DATENAME(MONTH,'2011-01-01') 返回 N'01';
-- DATENAME(DAY,'2011-01-01') 返回N'1'
CONVERT(VARCHAR,@datetime,@argument)?
作用:日期型转换为DATE型时,第三个参数指定转换格式。具体用法参考帮助手册