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

本文目录列表：

1、SQL Server旬时间粒度
2、SQL Server月有关时间粒度

3、SQL Server函数重构

4、总结语

5、参考清单列表

 

SQL Server旬时间粒度

 

    SQL Server不存在旬这个时间粒度，这个时间粒度在我们国家存在的。一个月分为上、中、下3旬，上旬和中旬均10天，分别对应第1天到第10天和第11天到第20天，下旬有可能8天、9天、10天和11天，从第21天到月末最后1天。从旬的定义得知旬是个日期时间范围的，那么如何实现将旬时间粒度时间值和一个对应的整数来实现相互转换呢？从旬的定义得知每旬都有个开始日期的，这个日期我们就叫做旬基准日期。如果同一旬的任意一个日期都对应一个相同的整数，那么再将一个整数转换为一个旬基准日期，这样我们可以实现旬基准日期和整数的相互转换。我们还可以得到每个日期在所在旬的第几天，也可以将得到一个月份每日期所在的旬索引（这个类似每周的任何一天对应的weekday值，从1到7分别对应星期一到星期日）。

    

     提供获得指定日期在当前月哪个旬的功能的函数，T-SQL代码如下：

IF OBJECT_ID(N'dbo.ufn_PeriodOfMonth', 'FN') IS NOT NULL
BEGIN
    DROP FUNCTION dbo.ufn_PeriodOfMonth;
END
GO
 
--==================================
-- 功能: 获得指定日期时间在当前月的旬索引
-- 说明: 从1开始计数，1、2、3分别对应上、中、下的旬索引。
-- 作者: XXX
-- 创建: yyyy-MM-dd
-- 修改: yyyy-MM-dd XXX 修改内容描述
-- 调用: SET @intPeriods = dbo.ufn_PeriodOfMonth('2008-01-14')
--==================================
CREATE FUNCTION [dbo].[ufn_PeriodOfMonth] 
(
    @dtmDate DATETIME            -- 指定的日期时间
) RETURNS TINYINT
    --$Encode$--
AS
BEGIN
    --当前月的日索引，从1开始计数，，包括1、2、3、……、28、29、30、31
    DECLARE @tintDayOfMonth AS TINYINT;
    SET @tintDayOfMonth = DAY(@dtmDate);
    -- 旬偏移索引，0:上旬，1:中旬，2:下旬
    DECLARE @tintPeriodOffsetIndexID AS INT;
    SET @tintPeriodOffsetIndexID = DAY(@dtmDate) / 10
 
    IF @tintDayOfMonth IN (10, 20, 30, 31)
    BEGIN
        SET @tintPeriodOffsetIndexID = @tintPeriodOffsetIndexID - 1;
    END
 
    RETURN @tintPeriodOffsetIndexID + 1;
END
GO

    

    提供获得指定日期在当前旬第几天的功能函数，T-SQL代码如下：

IF OBJECT_ID(N'dbo.ufn_DayOfPeriod', 'FN') IS NOT NULL
BEGIN
    DROP FUNCTION dbo.ufn_DayOfPeriod;
END
GO
 
--==================================
-- 功能: 获得指定日期时间在当前旬的日索引
-- 说明: 从1开始计数，包括1、2、3、……、8、9、10、11
-- 作者: XXX
-- 创建: yyyy-MM-dd
-- 修改: yyyy-MM-dd XXX 修改内容描述
-- 调用: SET @intPeriods = dbo.ufn_DayOfPeriod('2008-01-14')
--==================================
CREATE FUNCTION [dbo].[ufn_DayOfPeriod]
(
    @dtmDate DATETIME            -- 指定的日期时间
) RETURNS TINYINT
    --$Encode$--
AS
BEGIN
    RETURN DAY(@dtmDate) - (dbo.ufn_PeriodOfMonth(@dtmDate) - 1) * 10;
END
GO

    

    提供旬基准日期和整数相互转换的功能函数，T-SQL代码如下：

IF OBJECT_ID(N'dbo.ufn_Periods', 'FN') IS NOT NULL
BEGIN
    DROP FUNCTION dbo.ufn_Periods;
END
GO
 
--==================================
-- 功能: 获得指定日期时间基于基准日期的总旬数(一个整数值）
-- 说明: 如果指定的日期时间为NULL或者小于基准日期“1900-01-01”时，则其值默认基准日期；
--       结果值为非负整数，从0开始计数。
-- 作者: XXX
-- 创建: yyyy-MM-dd
-- 修改: yyyy-MM-dd XXX 修改内容描述
-- 调用: SET @intPeriods = dbo.ufn_Periods('2008-01-14')
--==================================
CREATE FUNCTION [dbo].[ufn_Periods] 
(
    @dtmDate DATETIME            -- 指定的日期时间
) RETURNS INT
    --$Encode$--
AS
BEGIN
    SET @dtmDate = dbo.ufn_GetValidDate(@dtmDate);
 
    -- 旬偏移索引ID,0:上旬，1:中旬，2:下旬
    DECLARE @tintPeriodOffsetIndexID AS INT;
    SET @tintPeriodOffsetIndexID = dbo.ufn_PeriodOfMonth(@dtmDate);
 
    -- datepart参数也可以为mm或m
    RETURN DATEDIFF(MONTH, '1900-01-01', @dtmDate) * 3 + @tintPeriodOffsetIndexID;
END
GO
 
IF OBJECT_ID(N'dbo.ufn_Periods2Date', 'FN') IS NOT NULL
BEGIN
    DROP FUNCTION dbo.ufn_Periods2Date;
END
GO
 
--==================================
-- 功能: 获得一个整数值基于基准日期对应的旬基准日期
-- 说明: 如果指定的整数值为NULL或为负整数时，则其值默认为0；
--       如果指定的整数值大于“9999-12-31”对应的整数值时，则其值默认设置为“9999-12-31”对应的整数值；
--       结果值为从基准日期开始计数的日期；
--       旬基准日期是指一个月份中第1天、第11天和第21天对应的日期，比如'2016-02'月份的3个旬基准日期分别为'2016-02-01','2016-02-11','2016-02-21'。
-- 作者: XXX
-- 创建: yyyy-MM-dd
-- 修改: yyyy-MM-dd XXX 修改内容描述
-- 调用: SET @dtmDate = dbo.ufn_Periods2Date(2705) --'1975-02-21'
--==================================
Create FUNCTION dbo.ufn_Periods2Date 
(
    @intPeriods INT
) RETURNS DATETIME
AS
BEGIN
    SET @intPeriods = dbo.ufn_GetValidDateNum(@intPeriods);
 
    DECLARE @intMaxPeriods AS INT;
    SET @intMaxPeriods = dbo.ufn_Periods('9999-12-31');
 
    IF @intPeriods >= @intMaxPeriods
    BEGIN
        SET @intPeriods = @intMaxPeriods;
    END
 
    -- datepart参数也可以为mm或m
    -- 以下注释的也可以，不过更接近相对日期+偏移数得到旬基准日期。
    --RETURN DATEADD(MONTH, @intPeriods / 3, '1900-01-01') + (@intPeriods - @intPeriods / 3 * 3) * 10;
    RETURN DATEADD(DAY,  (@intPeriods - @intPeriods / 3 * 3) * 10, DATEADD(MONTH, @intPeriods / 3, '1900-01-01'));
END
GO

 

    测试以上4个函数效果的T-SQL代码如下：

DECLARE @dtmDate AS DATETIME;
SET @dtmDate = '2016-01-08';
 
SELECT dbo.ufn_PeriodOfMonth(@dtmDate) AS 'Period IndexID Of Current Month(1:上旬，2:中旬，3:下旬)'
    ,dbo.ufn_DayOfPeriod(@dtmDate) AS 'Day IndexID Of Current Period(从1开始结束，包括1、2、3、……、10、11)';
 
SELECT dbo.ufn_Periods('1900-02-01') AS 'The Total Of Periods Base-on BaseDate"1900-01-01" '
    ,dbo.ufn_Periods2Date(1) AS 'Period Basedate Mapping 100'
GO

 

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

 

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代码如下：

IF OBJECT_ID(N'dbo.ufn_MonthOfQuarter', N'FN') IS NOT NULL
BEGIN
    DROP FUNCTION dbo.ufn_MonthOfQuarter;
END
GO
 
--==================================
-- 功能: 获得一个日期时间的月份在当前季度的月索引
-- 说明: 如果指定的整数值为NULL时，则其值默认设置为基准日期'1900-01-01'；
--       结果值从1开始计数，包括1、2、3。
-- 作者: XXX
-- 创建: yyyy-MM-dd
-- 修改: yyyy-MM-dd XXX 修改内容描述
-- 调用: SET @dtmDate = dbo.ufn_MonthOfQuarter(2008-01-01);
--==================================
CREATE FUNCTION dbo.ufn_MonthOfQuarter
(
    @dtmDate DATETIME            -- 指定的日期时间
) RETURNS TINYINT
AS
BEGIN
    SET @dtmDate = ISNULL(@dtmDate, '1900-01-01');
    DECLARE @tintMonthOfQuarter AS TINYINT;
    SET @tintMonthOfQuarter = 0;
 
    DECLARE @tintMonthOfYear AS TINYINT;
    SET @tintMonthOfYear = MONTH(@dtmDate);
 
    SET @tintMonthOfQuarter =  @tintMonthOfYear - @tintMonthOfYear / 3 * 3;
 
    IF @tintMonthOfQuarter = 0
    BEGIN
        SET @tintMonthOfQuarter = 3;
    END
 
    RETURN @tintMonthOfQuarter;
END
GO
 

 

    提供月基准日期和整数相互转换的功能函数，T-SQL代码入下：

IF OBJECT_ID(N'dbo.ufn_Months', 'FN') IS NOT NULL
BEGIN
    DROP FUNCTION dbo.ufn_Months;
END
GO
 
--==================================
-- 功能: 获得指定日期时间基于基准日期的总月数(一个整数值）
-- 说明: 如果指定的日期时间为NULL或者小于基准日期“1900-01-01”时，则其值默认基准日期
--       结果值为非负整数，从0开始计数。
-- 作者: XXX
-- 创建: yyyy-MM-dd
-- 修改: yyyy-MM-dd XXX 修改内容描述
-- 调用: SET @intMonths = dbo.ufn_Months('2008-01-14')
--==================================
CREATE FUNCTION dbo.ufn_Months 
(
    @dtmDate DATETIME
) RETURNS INT
AS
BEGIN
    IF @dtmDate IS NULL OR @dtmDate < '1900-01-01'
    BEGIN
        SET @dtmDate = '1900-01-01';
    END
 
    -- datepart参数也可以为mm或m
    RETURN DATEDIFF(MONTH, '1900-01-01', @dtmDate);
END
GO
 
IF OBJECT_ID(N'dbo.ufn_Months2Date', 'FN') IS NOT NULL
BEGIN
    DROP FUNCTION dbo.ufn_Months2Date;
END
GO
 
--==================================
-- 功能: 获得一个整数值基于基准日期对应的月基准日期
-- 说明: 如果指定的整数值为NULL或为负整数时，则其值默认为0；
--       如果指定的整数值大于“9999-12-31”对应的整数值时，则其值默认设置为“9999-12-31”对应的整数值；
--       结果值为从基准日期开始计数的日期；
--       月基准日期是指一个月份中第1天对应的日期，比如'2016-02'月份的月旬基准日期为'2016-02-01'。
-- 作者: XXX
-- 创建: yyyy-MM-dd
-- 修改: yyyy-MM-dd XXX 修改内容描述
-- 调用: SET @dtmDate = dbo.ufn_Months2Date(1296) --'2008-01-01'
--==================================
CREATE FUNCTION dbo.ufn_Months2Date 
(
    @intMonths INT
) RETURNS DATETIME
AS
BEGIN
    IF @intMonths IS NULL OR @intMonths < 0
    BEGIN
        SET @intMonths = 0;
    END
 
    DECLARE @intMaxMonths AS INT;
    SET @intMaxMonths = dbo.ufn_Months('9999-12-31');
 
    IF @intMonths >= @intMaxMonths
    BEGIN
        SET @intMonths = @intMaxMonths;
    END
 
    -- datepart参数也可以为mm或m
    RETURN DATEADD(MONTH, @intMonths, '1900-01-01');
END
GO

 

    测试有关月时间粒度的函数效果的T-SQL代码如下：

DECLARE @dtmDate AS DATETIME;
SET @dtmDate = '2016-01-08';
 
SELECT dbo.ufn_MonthOfQuarter(@dtmDate) AS 'Month IndexID Of Current Quarter(1:第1个月，2:第2个月，3:第3个月)'
    ,MONTH(@dtmDate) AS 'Month IndexID Of Current Year', DATEPART(MONTH, @dtmDate) AS 'Month IndexID Of Current Year（从1开始结束，包括1、2、3、……、10、12)）';
 
SELECT dbo.ufn_Months(@dtmDate) AS 'The Total Of Months Base-on BaseDate"1900-01-01"'
    ,dbo.ufn_Months2Date(2) AS 'Month Basedate Mapping 2';
GO

 

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

 

 

SQL Server函数重构

 

   在旬和月时间粒度已经提供的功能函数，我们会针对指定的日期时间和整数参数做进行相应的条件处理，发现了很多的重复T-SQL代码，为消除相似功能的重复代码，我将其封装在以下两个功能函数ufn_GetVlidDate和ufn_GetValidDateNum，分别提供对指定的日期时间和指定的日期数的有效性处理。

    

    提供以上功能函数的重构，T-SQL代码如下：

IF OBJECT_ID(N'dbo.ufn_GetValidDate', 'FN') IS NOT NULL
BEGIN
    DROP FUNCTION dbo.ufn_GetValidDate;
END
GO
 
--==================================
-- 功能: 获取有效日期
-- 说明: 指定的日期如果为NULL或者小于基准日期'1900-01-01'则默认设置为基准日期'1900-00-01'
-- 作者: XXX
-- 创建: yyyy-MM-dd
-- 修改: yyyy-MM-dd XXX 修改内容描述
-- 调用: SET @dtmDate = dbo.ufn_GetValidDate('2008-01-14')
--==================================
CREATE FUNCTION dbo.ufn_GetValidDate
(
    @dtmDate DATETIME            -- 指定的日期数
) RETURNS DATETIME
    --$Encode$--
BEGIN
    IF @dtmDate IS NULL OR @dtmDate < '1900-01-01'
    BEGIN
        RETURN '1900-01-01';
    END
 
    RETURN @dtmDate;
END
GO
 
IF OBJECT_ID(N'dbo.ufn_GetValidDateNum', 'FN') IS NOT NULL
BEGIN
    DROP FUNCTION dbo.ufn_GetValidDateNum;
END
GO
 
--==================================
-- 功能: 获取有效日期数
-- 说明: 指定的日期数如果为NULL或负数，则默认设置为0
-- 作者: XXX
-- 创建: yyyy-MM-dd
-- 修改: yyyy-MM-dd XXX 修改内容描述
-- 调用: SET @@intDateNum = dbo.ufn_GetValidDateNum(-1)
--==================================
CREATE FUNCTION dbo.ufn_GetValidDateNum
(
    @intDateNum INT            -- 指定的日期数
) RETURNS INT
    --$Encode$--
BEGIN
    IF @intDateNum IS NULL OR @intDateNum < 0
    BEGIN
        RETURN 0;
    END
 
    RETURN @intDateNum;
END
GO

 

    测试以上功能函数的T-SQL代码如下：

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'
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'
GO

 

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

 

注意：重复的代码实现了重构，本文中提供的重复代码所在的函数没有做变更的，需要的话请自己调整和修改代码。

 

    测试日期时间与加减运算符的效果，T-SQL代码如下：

DECLARE @dtmDate AS DATETIME;
SET @dtmDate = '';
 
SELECT @dtmDate AS '"" Default BaseDate"1900-01-01"';
SET @dtmDate = 0;
SELECT @dtmDate AS '0 Default BaseDate"1900-01-01"';
SET @dtmDate = @dtmDate + 1;
SELECT @dtmDate AS 'Add operator 等同dateadd(day, num, date)';
SET @dtmDate  = -1;
SELECT @dtmDate AS 'substract operator 等同dateadd(day, -1*num, date)';
GO

 

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

 

注意：以上的测试代码，SQL Server引擎默认使用了基准日期”1900-01-01“。

 

总结语

    

    本文在SQL Server时间粒度详解小节中，提供了获取指定日期所在旬的日索引功能和获取指定日期的所在旬在当前月的旬索引，以及旬基准日期和整数相互转换的功能函数。还实现了月基准日期和整数相互转换的功能函数，获取指定日期的月份在当前季度的月索引功能。还提供了T-SQL代码重构的例子，通过封装通用的功能函数来消除重复的T-SQL代码。也提供了日期时间数据类型的变量与加减运算符的测试代码，主要印证SQL Server引擎默认也是在使用基准日期”1900-01-01“。

 

参考清单列表

1、https://msdn.microsoft.com/zh-cn/library/ms186819(v=sql.90).aspx

2、https://msdn.microsoft.com/zh-cn/library/ms189794(v=sql.90).aspx