SQL Server时间粒度系列----第4节季、年时间粒度详解

本文目录列表：

1、SQL Server季时间粒度
2、SQL Server年时间粒度

3、总结语

4、参考清单列表

 

SQL Serve季时间粒度

 

    季时间粒度也即是季度时间粒度。一年每3个月是一个季，一年共4季，1月到3月是第1季、4月到6月是第2个季、依次顺延。季也是日期时间范围的，我们也会引入季基准日期，也就是每个季度的第一天。以下提供季基准日期和整数相互转换的功能的实现，还有获取指定日期时间所在当前年内的季索引，从1开始计数，包括1、2、3、4。

 

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

IF OBJECT_ID(N'dbo.ufn_Quarters', 'FN') IS NOT NULL
BEGIN
    DROP FUNCTION dbo.ufn_Quarters;
END
GO
 
--==================================
-- 功能: 获得指定日期时间基于基准日期的总季数(一个整数值）
-- 说明: 如果指定的日期时间为NULL或者小于基准日期“1900-01-01”时，则其值默认基准日期
--       结果值为非负整数，从0开始计数。
-- 作者: XXX
-- 创建: yyyy-MM-dd
-- 修改: yyyy-MM-dd XXX 修改内容描述
-- 调用: SET @sintQuarters = dbo.ufn_Quarters('2008-01-14')
--==================================
CREATE FUNCTION dbo.ufn_Quarters
(
    @dtmDate DATETIME                    -- 指定的日期时间
) RETURNS SMALLINT
    --$Encode$--
AS
BEGIN
    SET @dtmDate = dbo.ufn_GetValidDate(@dtmDate);
 
    -- datepart参数也可以为qq或q
    RETURN DATEDIFF(QUARTER, '1900-01-01', @dtmDate);
END
GO
 
IF OBJECT_ID(N'dbo.ufn_Quarters2Date', 'FN') IS NOT NULL
BEGIN
    DROP FUNCTION dbo.ufn_Quarters2Date;
END
GO
 
--==================================
-- 功能: 获得一个整数值基于基准日期对应的季基准日期
-- 说明: 如果指定的整数值为NULL或为负整数时，则其值默认为0；
--       如果指定的整数值大于“9999-12-31”对应的整数值时，则其值默认设置为“9999-12-31”对应的整数值；
--       结果值为从基准日期开始计数的日期；
--       季基准日期是指所在季度的第一个月份中第1天对应的日期，比如'2016-06-08'月份的姐旬基准日期为'2016-04-01'。
-- 作者: XXX
-- 创建: yyyy-MM-dd
-- 修改: yyyy-MM-dd XXX 修改内容描述
-- 调用: SET @dtmDate = dbo.ufn_Quarters2Date(300) --'1975-02-21'
--==================================
CREATE FUNCTION dbo.ufn_Quarters2Date 
(
    @sintQuarters SMALLINT
) RETURNS DATETIME
    --$Encode$--
AS
BEGIN
    SET @sintQuarters = dbo.ufn_GetValidDateNum(@sintQuarters);
 
    DECLARE @sintMaxQuarters AS SMALLINT;
    SET @sintMaxQuarters = dbo.ufn_Quarters('9999-12-31');
 
    IF @sintQuarters >= @sintMaxQuarters
    BEGIN
        SET @sintQuarters = @sintMaxQuarters;
    END
 
    -- datepart参数也可以为qq或q
    RETURN DATEADD(QUARTER, @sintQuarters, '1900-01-01')
END
GO

 

  

    获取指定日期时间在当前年的旬索引的功能函数，T-SQL代码如下:

IF OBJECT_ID(N'dbo.ufn_QuarterOfYear', 'FN') IS NOT NULL
BEGIN
    DROP FUNCTION dbo.ufn_QuarterOfYear;
END
GO

--==================================
-- 功能: 获取指定的日期时间所在当前年内的旬索引
-- 说明: 结果从1开始计数，1、2、3、4分别表示第1季度、第2季度、第3季度、第4季度。
-- 作者: XXX
-- 创建: yyyy-MM-dd
-- 修改: yyyy-MM-dd XXX 修改内容描述
-- 调用: SET @tintQuarterOfYear = dbo.ufn_QuarterOfYear('2016-01-11');
--==================================
CREATE FUNCTION dbo.ufn_QuarterOfYear
(
    @dtmDate DATETIME                --指定的日期时间
) RETURNS TINYINT
    --$Encode$--
AS
BEGIN
    -- datepart参数也可以为qq或q
    RETURN DATEPART(QUARTER, @dtmDate);
END
GO

 

    测试以上三个功能函数的效果，T-SQL代码如下：

DECLARE @dtmDate AS DATETIME;
SET @dtmDate = '2016-01-11';
 
SELECT @dtmDate AS 'The Current Date'
    ,dbo.ufn_Quarters(@dtmDate) AS 'The Total Of Quarters Base-on Basedate"1900-01-01"'
    ,dbo.ufn_Quarters2Date(dbo.ufn_Quarters(@dtmDate)) AS 'Quarter Basedate Mapping'
    ,dbo.ufn_QuarterOfYear(@dtmDate) AS 'The Quarter IndexID,Starting With 1';
 
SET @dtmDate = '2016-09-11';
 
SELECT @dtmDate AS 'The Current Date'
    ,dbo.ufn_Quarters(@dtmDate) AS 'The Total Of Quarters Base-on Basedate"1900-01-01"'
    ,dbo.ufn_Quarters2Date(dbo.ufn_Quarters(@dtmDate)) AS 'Quarter Basedate Mapping'
    ,dbo.ufn_QuarterOfYear(@dtmDate) AS 'The Quarter IndexID,Starting With 1'
GO
  

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

 

SQL Server年时间粒度

 

    年时间粒度很容易理解的，在SQL Server提供的有关日期时间数据类型中，能够支持日期部分的最大范围区间是[0000-01-01,9999-12-31]，那么指定日期在这个日期范围的年索引是从1开始，包括1、2、3、……、9998、9999。年基准日期也即是当前年的第一天，比如"2016-01-11"所在当前年的年基准日期是”2016-01-01“，提供年基准日期和整数相互转换的功能函数实现。

    

    获取指定的日期时间所在日期的年索引的功能函数，T-SQL代码如下：

IF OBJECT_ID(N'dbo.ufn_YearOfDate', 'FN') IS NOT NULL
BEGIN
    DROP FUNCTION dbo.ufn_YearOfDate;
END
GO
 
--==================================
-- 功能: 获取指定的日期时间所在日期内的年索引
-- 说明: 结果从1开始计数，1、2、3、……、9998、9999。
-- 作者: XXX
-- 创建: yyyy-MM-dd
-- 修改: yyyy-MM-dd XXX 修改内容描述
-- 调用: SET @sintYearOfDate = dbo.ufn_YearsOfDate('2016-01-11');
--==================================
CREATE FUNCTION dbo.ufn_YearOfDate
(
    @dtmDate DATETIME                --指定的日期时间
) RETURNS SMALLINT
    --$Encode$--
AS
BEGIN
    RETURN YEAR(@dtmDate);
END
GO
 

 

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

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

    

    测试以上3个功能函数的效果，T-SQL代码如下：

DECLARE @dtmDate AS DATETIME;
SET @dtmDate = '2016-01-11';
 
SELECT @dtmDate AS 'The Current Date'
    ,dbo.ufn_Years(@dtmDate) AS 'The Total Of Years Base-on Basedate"1900-01-01"'
    ,dbo.ufn_Years2Date(dbo.ufn_Years(@dtmDate)) AS 'Year Basedate Mapping'
    ,dbo.ufn_YearOfDate(@dtmDate) AS 'The Year IndexID,Starting With 1';
 
SET @dtmDate = '2016-09-11';
 
SELECT @dtmDate AS 'The Current Date'
    ,dbo.ufn_Years(@dtmDate) AS 'The Total Of Years Base-on Basedate"1900-01-01"'
    ,dbo.ufn_Years2Date(dbo.ufn_Years(@dtmDate)) AS 'Year Basedate Mapping'
    ,dbo.ufn_YearOfDate(@dtmDate) AS 'The Year IndexID,Starting With 1';
GO

 

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

 

总结语

    

    本文主要提供了季和年这两个时间粒度的讲解，也分别提供的其基准日期和整数相互转换的功能实现，还有各自有关的索引功能函数。之前的博文也有获取比如旬内日索引、月内日索引、月内旬索引等等的相关功能函数，这些都不是基于基准日期”1900-01-01“，都是在SQL Server能支持的最大的日期部分的范围区间【0001-01-01,9999-12-31】的。

    

    以上有关季和年有关的功能函数，使用了ufn_GetValidDate和ufn_GetValidDateNum这两个通用同能函数，可以参考SQL Server时间粒度系列----第3节旬、月时间粒度详解，里面有具体的实现。

 

参考清单列表

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