T-SQL创建时间维

use POC
go

IF OBJECT_ID('dbo.isLeapYear','function') IS NOT NULL
	DROP function dbo.isLeapYear
IF OBJECT_ID('dbo.TD_CALENDAR','TABLE') IS NOT NULL
	DROP TABLE dbo.TD_CALENDAR
go

--创建维表
--@date_int,@date_v,@date_name,@year_int,@year_name,@quarter_int,@quarter_name,@month_int,@month_name,null
CREATE 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 int
AS

begin
	declare @isLeap int = 0;
	if (datediff(day,cast(@year   as   char)+ '-1-1 ',cast(@year+1   as   char)+ '-1-1 '))  = 366
		set @isLeap = 1
	else  
		set @isLeap = 0   
	  
	return @isLeap

end
go



declare @date_v date = '2000-12-31';	--设置起始年份,实际从下一年开始
declare @yearCount int	=1				--@yearCount 年数的循环计数器
declare @numbersOfYear int = 10 ;		--@yearCount 设置创建维表的年数

while @yearCount  <= @numbersOfYear

begin
	declare @i int = 0;					--循环计数器
	declare @iCount int;				--平年@iCount = 364,闰年@iCount = 365
	declare @date_int int;				--int型的date key 如20010101
	declare @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 = 365
	else 
		set @iCount = 364
		
	while @i <= @iCount
	begin
		--从初始化值的下一年的第一天开始,本应为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_CALENDAR
		VALUES(@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;
end
go


--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型时,第三个参数指定转换格式。具体用法参考帮助手册