本文目录列表：
SQL Server不存在旬这个时间粒度，这个时间粒度在我们国家存在的。一个月分为上、中、下3旬，上旬和中旬均10天，分别对应第1天到第10天和第11天到第20天，下旬有可能8天、9天、10天和11天，从第21天到月末最后1天。从旬的定义得知旬是个日期时间范围的，那么如何实现将旬时间粒度时间值和一个对应的整数来实现相互转换呢？从旬的定义得知每旬都有个开始日期的，这个日期我们就叫做旬基准日期。如果同一旬的任意一个日期都对应一个相同的整数，那么再将一个整数转换为一个旬基准日期，这样我们可以实现旬基准日期和整数的相互转换。我们还可以得到每个日期在所在旬的第几天，也可以将得到一个月份每日期所在的旬索引（这个类似每周的任何一天对应的weekday值，从1到7分别对应星期一到星期日）。
提供获得指定日期在当前月哪个旬的功能的函数，T-SQL代码如下：
1 IF OBJECT_ID(N'dbo.ufn_PeriodOfMonth', 'FN') IS NOT NULL 2 BEGIN 3 DROP FUNCTION dbo.ufn_PeriodOfMonth; 4 END 5 GO 6 7 --================================== 8 -- 功能: 获得指定日期时间在当前月的旬索引 9 -- 说明: 从1开始计数，1、2、3分别对应上、中、下的旬索引。10 -- 作者: XXX11 -- 创建: yyyy-MM-dd12 -- 修改: yyyy-MM-dd XXX 修改内容描述13 -- 调用: SET @intPeriods = dbo.ufn_PeriodOfMonth('2008-01-14')14 --==================================15 CREATE FUNCTION [dbo].[ufn_PeriodOfMonth] 16 (17 @dtmDate DATETIME -- 指定的日期时间18 ) RETURNS TINYINT19 --$Encode$--20 AS21 BEGIN22 --当前月的日索引，从1开始计数，，包括1、2、3、……、28、29、30、3123 DECLARE @tintDayOfMonth AS TINYINT;24 SET @tintDayOfMonth = DAY(@dtmDate);25 -- 旬偏移索引，0:上旬，1:中旬，2:下旬26 DECLARE @tintPeriodOffsetIndexID AS INT;27 SET @tintPeriodOffsetIndexID = DAY(@dtmDate) / 1028 29 IF @tintDayOfMonth IN (10, 20, 30, 31)30 BEGIN31 SET @tintPeriodOffsetIndexID = @tintPeriodOffsetIndexID - 1;32 END33 34 RETURN @tintPeriodOffsetIndexID + 1;35 END36 GO
提供获得指定日期在当前旬第几天的功能函数，T-SQL代码如下：
1 IF OBJECT_ID(N'dbo.ufn_DayOfPeriod', 'FN') IS NOT NULL 2 BEGIN 3 DROP FUNCTION dbo.ufn_DayOfPeriod; 4 END 5 GO 6 7 --================================== 8 -- 功能: 获得指定日期时间在当前旬的日索引 9 -- 说明: 从1开始计数，包括1、2、3、……、8、9、10、1110 -- 作者: XXX11 -- 创建: yyyy-MM-dd12 -- 修改: yyyy-MM-dd XXX 修改内容描述13 -- 调用: SET @intPeriods = dbo.ufn_DayOfPeriod('2008-01-14')14 --==================================15 CREATE FUNCTION [dbo].[ufn_DayOfPeriod]16 (17 @dtmDate DATETIME -- 指定的日期时间18 ) RETURNS TINYINT19 --$Encode$--20 AS21 BEGIN22 RETURN DAY(@dtmDate) - (dbo.ufn_PeriodOfMonth(@dtmDate) - 1) * 10;23 END24 GO
提供旬基准日期和整数相互转换的功能函数，T-SQL代码如下：
1 IF OBJECT_ID(N'dbo.ufn_Periods', 'FN') IS NOT NULL 2 BEGIN 3 DROP FUNCTION dbo.ufn_Periods; 4 END 5 GO 6 7 --================================== 8 -- 功能: 获得指定日期时间基于基准日期的总旬数(一个整数值） 9 -- 说明: 如果指定的日期时间为NULL或者小于基准日期“1900-01-01”时，则其值默认基准日期；10 -- 结果值为非负整数，从0开始计数。11 -- 作者: XXX12 -- 创建: yyyy-MM-dd13 -- 修改: yyyy-MM-dd XXX 修改内容描述14 -- 调用: SET @intPeriods = dbo.ufn_Periods('2008-01-14')15 --==================================16 CREATE FUNCTION [dbo].[ufn_Periods] 17 (18 @dtmDate DATETIME -- 指定的日期时间19 ) RETURNS INT20 --$Encode$--21 AS22 BEGIN23 SET @dtmDate = dbo.ufn_GetValidDate(@dtmDate);24 25 -- 旬偏移索引ID,0:上旬，1:中旬，2:下旬26 DECLARE @tintPeriodOffsetIndexID AS INT;27 SET @tintPeriodOffsetIndexID = dbo.ufn_PeriodOfMonth(@dtmDate);28 29 -- datepart参数也可以为mm或m30 RETURN DATEDIFF(MONTH, '1900-01-01', @dtmDate) * 3 + @tintPeriodOffsetIndexID;31 END32 GO33 34 IF OBJECT_ID(N'dbo.ufn_Periods2Date', 'FN') IS NOT NULL35 BEGIN36 DROP FUNCTION dbo.ufn_Periods2Date;37 END38 GO39 40 --==================================41 -- 功能: 获得一个整数值基于基准日期对应的旬基准日期42 -- 说明: 如果指定的整数值为NULL或为负整数时，则其值默认为0；43 -- 如果指定的整数值大于“9999-12-31”对应的整数值时，则其值默认设置为“9999-12-31”对应的整数值；44 -- 结果值为从基准日期开始计数的日期；45 -- 旬基准日期是指一个月份中第1天、第11天和第21天对应的日期，比如'2016-02'月份的3个旬基准日期分别为'2016-02-01','2016-02-11','2016-02-21'。46 -- 作者: XXX47 -- 创建: yyyy-MM-dd48 -- 修改: yyyy-MM-dd XXX 修改内容描述49 -- 调用: SET @dtmDate = dbo.ufn_Periods2Date(2705) --'1975-02-21'50 --==================================51 Create FUNCTION dbo.ufn_Periods2Date 52 (53 @intPeriods INT54 ) RETURNS DATETIME55 AS56 BEGIN57 SET @intPeriods = dbo.ufn_GetValidDateNum(@intPeriods);58 59 DECLARE @intMaxPeriods AS INT;60 SET @intMaxPeriods = dbo.ufn_Periods('9999-12-31');61 62 IF @intPeriods >= @intMaxPeriods63 BEGIN64 SET @intPeriods = @intMaxPeriods;65 END66 67 -- datepart参数也可以为mm或m68 -- 以下注释的也可以，不过更接近相对日期+偏移数得到旬基准日期。69 --RETURN DATEADD(MONTH, @intPeriods / 3, '1900-01-01') + (@intPeriods - @intPeriods / 3 * 3) * 10;70 RETURN DATEADD(DAY, (@intPeriods - @intPeriods / 3 * 3) * 10, DATEADD(MONTH, @intPeriods / 3, '1900-01-01'));71 END72 GO
测试以上4个函数效果的T-SQL代码如下：
1 DECLARE @dtmDate AS DATETIME;2 SET @dtmDate = '2016-01-08';3 4 SELECT dbo.ufn_PeriodOfMonth(@dtmDate) AS 'Period IndexID Of Current Month(1:上旬，2:中旬，3:下旬)'5 ,dbo.ufn_DayOfPeriod(@dtmDate) AS 'Day IndexID Of Current Period(从1开始结束，包括1、2、3、……、10、11)';6 7 SELECT dbo.ufn_Periods('1900-02-01') AS 'The Total Of Periods Base-on BaseDate"1900-01-01" '8 ,dbo.ufn_Periods2Date(1) AS 'Period Basedate Mapping 100'9 GO
执行后的查询结果如下图：
在旬时间粒度小节中，我们引入了旬基准日期的概念，针对月基准日期的定义就是每月的第一天代表的日期时间值，比如”2016-01-08“所在的月基准日期就是”2016-01-01“。我们也提供月基准日期和整数相互转换的功能函数。指定日期的月份在当前季度中的月索引，从1开始计数，包括1、2、3。SQL Server提供了month(@dtmDateTime）或datepart(month, @dtmDateTime)获取指定日期所在月份（当前年的份索引，从1开始计数，包括1,、2、3、……、10、11、12）。
提供获得指定日期的月份在当前季度的月索引，T_SQL代码如下：
1 IF OBJECT_ID(N'dbo.ufn_MonthOfQuarter', N'FN') IS NOT NULL 2 BEGIN 3 DROP FUNCTION dbo.ufn_MonthOfQuarter; 4 END 5 GO 6 7 --================================== 8 -- 功能: 获得一个日期时间的月份在当前季度的月索引 9 -- 说明: 如果指定的整数值为NULL时，则其值默认设置为基准日期'1900-01-01'；10 -- 结果值从1开始计数，包括1、2、3。11 -- 作者: XXX12 -- 创建: yyyy-MM-dd13 -- 修改: yyyy-MM-dd XXX 修改内容描述14 -- 调用: SET @dtmDate = dbo.ufn_MonthOfQuarter(2008-01-01);15 --==================================16 CREATE FUNCTION dbo.ufn_MonthOfQuarter17 (18 @dtmDate DATETIME -- 指定的日期时间19 ) RETURNS TINYINT20 AS21 BEGIN22 SET @dtmDate = ISNULL(@dtmDate, '1900-01-01');23 DECLARE @tintMonthOfQuarter AS TINYINT;24 SET @tintMonthOfQuarter = 0;25 26 DECLARE @tintMonthOfYear AS TINYINT;27 SET @tintMonthOfYear = MONTH(@dtmDate);28 29 SET @tintMonthOfQuarter = @tintMonthOfYear - @tintMonthOfYear / 3 * 3;30 31 IF @tintMonthOfQuarter = 032 BEGIN33 SET @tintMonthOfQuarter = 3;34 END35 36 RETURN @tintMonthOfQuarter;37 END38 GO39
提供月基准日期和整数相互转换的功能函数，T-SQL代码入下：
1 IF OBJECT_ID(N'dbo.ufn_Months', 'FN') IS NOT NULL 2 BEGIN 3 DROP FUNCTION dbo.ufn_Months; 4 END 5 GO 6 7 --================================== 8 -- 功能: 获得指定日期时间基于基准日期的总月数(一个整数值） 9 -- 说明: 如果指定的日期时间为NULL或者小于基准日期“1900-01-01”时，则其值默认基准日期10 -- 结果值为非负整数，从0开始计数。11 -- 作者: XXX12 -- 创建: yyyy-MM-dd13 -- 修改: yyyy-MM-dd XXX 修改内容描述14 -- 调用: SET @intMonths = dbo.ufn_Months('2008-01-14')15 --==================================16 CREATE FUNCTION dbo.ufn_Months 17 (18 @dtmDate DATETIME19 ) RETURNS INT20 AS21 BEGIN22 IF @dtmDate IS NULL OR @dtmDate < '1900-01-01'23 BEGIN24 SET @dtmDate = '1900-01-01';25 END26 27 -- datepart参数也可以为mm或m28 RETURN DATEDIFF(MONTH, '1900-01-01', @dtmDate);29 END30 GO31 32 IF OBJECT_ID(N'dbo.ufn_Months2Date', 'FN') IS NOT NULL33 BEGIN34 DROP FUNCTION dbo.ufn_Months2Date;35 END36 GO37 38 --==================================39 -- 功能: 获得一个整数值基于基准日期对应的月基准日期40 -- 说明: 如果指定的整数值为NULL或为负整数时，则其值默认为0；41 -- 如果指定的整数值大于“9999-12-31”对应的整数值时，则其值默认设置为“9999-12-31”对应的整数值；42 -- 结果值为从基准日期开始计数的日期；43 -- 月基准日期是指一个月份中第1天对应的日期，比如'2016-02'月份的月旬基准日期为'2016-02-01'。44 -- 作者: XXX45 -- 创建: yyyy-MM-dd46 -- 修改: yyyy-MM-dd XXX 修改内容描述47 -- 调用: SET @dtmDate = dbo.ufn_Months2Date(1296) --'2008-01-01'48 --==================================49 CREATE FUNCTION dbo.ufn_Months2Date 50 (51 @intMonths INT52 ) RETURNS DATETIME53 AS54 BEGIN55 IF @intMonths IS NULL OR @intMonths < 056 BEGIN57 SET @intMonths = 0;58 END59 60 DECLARE @intMaxMonths AS INT;61 SET @intMaxMonths = dbo.ufn_Months('9999-12-31');62 63 IF @intMonths >= @intMaxMonths64 BEGIN65 SET @intMonths = @intMaxMonths;66 END67 68 -- datepart参数也可以为mm或m69 RETURN DATEADD(MONTH, @intMonths, '1900-01-01');70 END71 GO
测试有关月时间粒度的函数效果的T-SQL代码如下：
1 DECLARE @dtmDate AS DATETIME;2 SET @dtmDate = '2016-01-08';3 4 SELECT dbo.ufn_MonthOfQuarter(@dtmDate) AS 'Month IndexID Of Current Quarter(1:第1个月，2:第2个月，3:第3个月)'5 ,MONTH(@dtmDate) AS 'Month IndexID Of Current Year', DATEPART(MONTH, @dtmDate) AS 'Month IndexID Of Current Year（从1开始结束，包括1、2、3、……、10、12)）';6 7 SELECT dbo.ufn_Months(@dtmDate) AS 'The Total Of Months Base-on BaseDate"1900-01-01"'8 ,dbo.ufn_Months2Date(2) AS 'Month Basedate Mapping 2';9 GO
执行后的查询结果如下图：
在旬和月时间粒度已经提供的功能函数，我们会针对指定的日期时间和整数参数做进行相应的条件处理，发现了很多的重复T-SQL代码，为消除相似功能的重复代码，我将其封装在以下两个功能函数ufn_GetVlidDate和ufn_GetValidDateNum，分别提供对指定的日期时间和指定的日期数的有效性处理。
提供以上功能函数的重构，T-SQL代码如下：
1 IF OBJECT_ID(N'dbo.ufn_GetValidDate', 'FN') IS NOT NULL 2 BEGIN 3 DROP FUNCTION dbo.ufn_GetValidDate; 4 END 5 GO 6 7 --================================== 8 -- 功能: 获取有效日期 9 -- 说明: 指定的日期如果为NULL或者小于基准日期'1900-01-01'则默认设置为基准日期'1900-00-01'10 -- 作者: XXX11 -- 创建: yyyy-MM-dd12 -- 修改: yyyy-MM-dd XXX 修改内容描述13 -- 调用: SET @dtmDate = dbo.ufn_GetValidDate('2008-01-14')14 --==================================15 CREATE FUNCTION dbo.ufn_GetValidDate16 (17 @dtmDate DATETIME -- 指定的日期数18 ) RETURNS DATETIME19 --$Encode$--20 BEGIN21 IF @dtmDate IS NULL OR @dtmDate < '1900-01-01'22 BEGIN23 RETURN '1900-01-01';24 END25 26 RETURN @dtmDate;27 END28 GO29 30 IF OBJECT_ID(N'dbo.ufn_GetValidDateNum', 'FN') IS NOT NULL31 BEGIN32 DROP FUNCTION dbo.ufn_GetValidDateNum;33 END34 GO35 36 --==================================37 -- 功能: 获取有效日期数38 -- 说明: 指定的日期数如果为NULL或负数，则默认设置为039 -- 作者: XXX40 -- 创建: yyyy-MM-dd41 -- 修改: yyyy-MM-dd XXX 修改内容描述42 -- 调用: SET @@intDateNum = dbo.ufn_GetValidDateNum(-1)43 --==================================44 CREATE FUNCTION dbo.ufn_GetValidDateNum45 (46 @intDateNum INT -- 指定的日期数47 ) RETURNS INT48 --$Encode$--49 BEGIN50 IF @intDateNum IS NULL OR @intDateNum < 051 BEGIN52 RETURN 0;53 END54 55 RETURN @intDateNum;56 END57 GO
测试以上功能函数的T-SQL代码如下：
1 SELECT dbo.ufn_GetValidDate(NULL) AS 'NULL Default BaseDate"1900-01-01"', dbo.ufn_GetValidDate('1899-12-31') AS 'Less Than BaseDate"1900-01-01" Default BaseDate"1900-01-01"', dbo.ufn_GetValidDate('2016-01-08') AS '2016-01-08'2 SELECT dbo.ufn_GetValidDateNum(NULL) AS 'NULL Defult 0', dbo.ufn_GetValidDateNum(-1) AS 'Less Than 0 Defult 0', dbo.ufn_GetValidDateNum(12) AS '12'3 GO
执行后的查询结果如下图：
注意：重复的代码实现了重构，本文中提供的重复代码所在的函数没有做变更的，需要的话请自己调整和修改代码。
测试日期时间与加减运算符的效果，T-SQL代码如下：
1 DECLARE @dtmDate AS DATETIME; 2 SET @dtmDate = ''; 3 4 SELECT @dtmDate AS '"" Default BaseDate"1900-01-01"'; 5 SET @dtmDate = 0; 6 SELECT @dtmDate AS '0 Default BaseDate"1900-01-01"'; 7 SET @dtmDate = @dtmDate + 1; 8 SELECT @dtmDate AS 'Add operator 等同dateadd(day, num, date)'; 9 SET @dtmDate = -1;10 SELECT @dtmDate AS 'substract operator 等同dateadd(day, -1*num, date)';11 GO
执行后的查询结果如下图：
注意：以上的测试代码，SQL Server引擎默认使用了基准日期”1900-01-01“。
本文在SQL Server时间粒度详解小节中，提供了获取指定日期所在旬的日索引功能和获取指定日期的所在旬在当前月的旬索引，以及旬基准日期和整数相互转换的功能函数。还实现了月基准日期和整数相互转换的功能函数，获取指定日期的月份在当前季度的月索引功能。还提供了T-SQL代码重构的例子，通过封装通用的功能函数来消除重复的T-SQL代码。也提供了日期时间数据类型的变量与加减运算符的测试代码，主要印证SQL Server引擎默认也是在使用基准日期”1900-01-01“。