use POCgoIF OBJECT_ID('dbo.isLeapYear','function') IS NOT NULL DROP function dbo.isLeapYearIF OBJECT_ID('dbo.TD_CALENDAR','TABLE') IS NOT NULL DROP TABLE dbo.TD_CALENDARgo--创建维表[email protected]_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 intASbegin 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 @isLeapendgodeclare @date_v date = '2000-12-31'; --设置起始年份,实际从下一年开始declare @yearCount int =1 [email protected] 年数的循环计数器declare @numbersOfYear int = 10 ; [email protected] 设置创建维表的年数while @yearCount <= @numbersOfYearbegin declare @i int = 0; --循环计数器 declare @iCount int; [email protected] = 364,[email protected] = 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;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型时,第三个参数指定转换格式。具体用法参考帮助手册