SQL Server时间粒度系列----第3节旬、月时间粒度详解

本文目录列表:
 
SQL Server旬时间粒度
 
    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开始计数,12、3分别对应上、中、下的旬索引。
10 -- 作者: XXX
11 -- 创建: yyyy-MM-dd
12 -- 修改: 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 TINYINT
19     --$Encode$--
20 AS
21 BEGIN
22     --当前月的日索引,从1开始计数,,包括1、23、……、28293031
23     DECLARE @tintDayOfMonth AS TINYINT;
24     SET @tintDayOfMonth = DAY(@dtmDate);
25     -- 旬偏移索引,0:上旬,1:中旬,2:下旬
26     DECLARE @tintPeriodOffsetIndexID AS INT;
27     SET @tintPeriodOffsetIndexID = DAY(@dtmDate) / 10
28  
29     IF @tintDayOfMonth IN (10, 20, 30, 31)
30     BEGIN
31         SET @tintPeriodOffsetIndexID = @tintPeriodOffsetIndexID - 1;
32     END
33  
34     RETURN @tintPeriodOffsetIndexID + 1;
35 END
36 GO
View Code

    

    提供获得指定日期在当前旬第几天的功能函数,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、11
10 -- 作者: XXX
11 -- 创建: yyyy-MM-dd
12 -- 修改: 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 TINYINT
19     --$Encode$--
20 AS
21 BEGIN
22     RETURN DAY(@dtmDate) - (dbo.ufn_PeriodOfMonth(@dtmDate) - 1) * 10;
23 END
24 GO
View Code

    

    提供旬基准日期和整数相互转换的功能函数,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 -- 作者: XXX
12 -- 创建: yyyy-MM-dd
13 -- 修改: 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 INT
20     --$Encode$--
21 AS
22 BEGIN
23     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或m
30     RETURN DATEDIFF(MONTH, '1900-01-01', @dtmDate) * 3 + @tintPeriodOffsetIndexID;
31 END
32 GO
33  
34 IF OBJECT_ID(N'dbo.ufn_Periods2Date', 'FN') IS NOT NULL
35 BEGIN
36     DROP FUNCTION dbo.ufn_Periods2Date;
37 END
38 GO
39  
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 -- 作者: XXX
47 -- 创建: yyyy-MM-dd
48 -- 修改: yyyy-MM-dd XXX 修改内容描述
49 -- 调用: SET @dtmDate = dbo.ufn_Periods2Date(2705) --'1975-02-21'
50 --==================================
51 Create FUNCTION dbo.ufn_Periods2Date 
52 (
53     @intPeriods INT
54 ) RETURNS DATETIME
55 AS
56 BEGIN
57     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 >= @intMaxPeriods
63     BEGIN
64         SET @intPeriods = @intMaxPeriods;
65     END
66  
67     -- datepart参数也可以为mm或m
68     -- 以下注释的也可以,不过更接近相对日期+偏移数得到旬基准日期。
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 END
72 GO
View Code

 

    测试以上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
View Code

 

    执行后的查询结果如下图:

 
SQL Server月有关时间粒度
 
    在旬时间粒度小节中,我们引入了旬基准日期的概念,针对月基准日期的定义就是每月的第一天代表的日期时间值,比如”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 -- 作者: XXX
12 -- 创建: yyyy-MM-dd
13 -- 修改: yyyy-MM-dd XXX 修改内容描述
14 -- 调用: SET @dtmDate = dbo.ufn_MonthOfQuarter(2008-01-01);
15 --==================================
16 CREATE FUNCTION dbo.ufn_MonthOfQuarter
17 (
18     @dtmDate DATETIME            -- 指定的日期时间
19 ) RETURNS TINYINT
20 AS
21 BEGIN
22     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 = 0
32     BEGIN
33         SET @tintMonthOfQuarter = 3;
34     END
35  
36     RETURN @tintMonthOfQuarter;
37 END
38 GO
39  
View Code

 

    提供月基准日期和整数相互转换的功能函数,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 -- 作者: XXX
12 -- 创建: yyyy-MM-dd
13 -- 修改: yyyy-MM-dd XXX 修改内容描述
14 -- 调用: SET @intMonths = dbo.ufn_Months('2008-01-14')
15 --==================================
16 CREATE FUNCTION dbo.ufn_Months 
17 (
18     @dtmDate DATETIME
19 ) RETURNS INT
20 AS
21 BEGIN
22     IF @dtmDate IS NULL OR @dtmDate < '1900-01-01'
23     BEGIN
24         SET @dtmDate = '1900-01-01';
25     END
26  
27     -- datepart参数也可以为mm或m
28     RETURN DATEDIFF(MONTH, '1900-01-01', @dtmDate);
29 END
30 GO
31  
32 IF OBJECT_ID(N'dbo.ufn_Months2Date', 'FN') IS NOT NULL
33 BEGIN
34     DROP FUNCTION dbo.ufn_Months2Date;
35 END
36 GO
37  
38 --==================================
39 -- 功能: 获得一个整数值基于基准日期对应的月基准日期
40 -- 说明: 如果指定的整数值为NULL或为负整数时,则其值默认为0;
41 --       如果指定的整数值大于“9999-12-31”对应的整数值时,则其值默认设置为“9999-12-31”对应的整数值;
42 --       结果值为从基准日期开始计数的日期;
43 --       月基准日期是指一个月份中第1天对应的日期,比如'2016-02'月份的月旬基准日期为'2016-02-01'。
44 -- 作者: XXX
45 -- 创建: yyyy-MM-dd
46 -- 修改: yyyy-MM-dd XXX 修改内容描述
47 -- 调用: SET @dtmDate = dbo.ufn_Months2Date(1296) --'2008-01-01'
48 --==================================
49 CREATE FUNCTION dbo.ufn_Months2Date 
50 (
51     @intMonths INT
52 ) RETURNS DATETIME
53 AS
54 BEGIN
55     IF @intMonths IS NULL OR @intMonths < 0
56     BEGIN
57         SET @intMonths = 0;
58     END
59  
60     DECLARE @intMaxMonths AS INT;
61     SET @intMaxMonths = dbo.ufn_Months('9999-12-31');
62  
63     IF @intMonths >= @intMaxMonths
64     BEGIN
65         SET @intMonths = @intMaxMonths;
66     END
67  
68     -- datepart参数也可以为mm或m
69     RETURN DATEADD(MONTH, @intMonths, '1900-01-01');
70 END
71 GO
View Code

 

    测试有关月时间粒度的函数效果的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
View Code

 

    执行后的查询结果如下图:
 
 
SQL Server函数重构
 
   在旬和月时间粒度已经提供的功能函数,我们会针对指定的日期时间和整数参数做进行相应的条件处理,发现了很多的重复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 -- 作者: XXX
11 -- 创建: yyyy-MM-dd
12 -- 修改: yyyy-MM-dd XXX 修改内容描述
13 -- 调用: SET @dtmDate = dbo.ufn_GetValidDate('2008-01-14')
14 --==================================
15 CREATE FUNCTION dbo.ufn_GetValidDate
16 (
17     @dtmDate DATETIME            -- 指定的日期数
18 ) RETURNS DATETIME
19     --$Encode$--
20 BEGIN
21     IF @dtmDate IS NULL OR @dtmDate < '1900-01-01'
22     BEGIN
23         RETURN '1900-01-01';
24     END
25  
26     RETURN @dtmDate;
27 END
28 GO
29  
30 IF OBJECT_ID(N'dbo.ufn_GetValidDateNum', 'FN') IS NOT NULL
31 BEGIN
32     DROP FUNCTION dbo.ufn_GetValidDateNum;
33 END
34 GO
35  
36 --==================================
37 -- 功能: 获取有效日期数
38 -- 说明: 指定的日期数如果为NULL或负数,则默认设置为0
39 -- 作者: XXX
40 -- 创建: yyyy-MM-dd
41 -- 修改: yyyy-MM-dd XXX 修改内容描述
42 -- 调用: SET @@intDateNum = dbo.ufn_GetValidDateNum(-1)
43 --==================================
44 CREATE FUNCTION dbo.ufn_GetValidDateNum
45 (
46     @intDateNum INT            -- 指定的日期数
47 ) RETURNS INT
48     --$Encode$--
49 BEGIN
50     IF @intDateNum IS NULL OR @intDateNum < 0
51     BEGIN
52         RETURN 0;
53     END
54  
55     RETURN @intDateNum;
56 END
57 GO
View Code

 

    测试以上功能函数的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
View Code

 

    执行后的查询结果如下图:
 
注意:重复的代码实现了重构,本文中提供的重复代码所在的函数没有做变更的,需要的话请自己调整和修改代码。
 
    测试日期时间与加减运算符的效果,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
View Code

 

    执行后的查询结果如下图:
 
注意:以上的测试代码,SQL Server引擎默认使用了基准日期”1900-01-01“。
 
总结语
    
    本文在SQL Server时间粒度详解小节中,提供了获取指定日期所在旬的日索引功能和获取指定日期的所在旬在当前月的旬索引,以及旬基准日期和整数相互转换的功能函数。还实现了月基准日期和整数相互转换的功能函数,获取指定日期的月份在当前季度的月索引功能。还提供了T-SQL代码重构的例子,通过封装通用的功能函数来消除重复的T-SQL代码。也提供了日期时间数据类型的变量与加减运算符的测试代码,主要印证SQL Server引擎默认也是在使用基准日期”1900-01-01“。
 
参考清单列表
posted @ 2016-01-08 15:08  剑走江湖  阅读(1373)  评论(0编辑  收藏  举报