SQL Server中自定义分割月的方法及SQL代码指什么

发布时间：2023-04-20 14:23:30 所属栏目：MsSql教程来源：

导读：就跟大家聊聊有关“SQL Server中自定义分割月的方法及SQL代码是什么”的内容，可能很多人都不太了解，为了让大家认识和更进一步的了解，小编给大家总结了以下内容，希望这篇“SQL Server中自定义分割

就跟大家聊聊有关“SQL Server中自定义分割月的方法及SQL代码是什么”的内容，可能很多人都不太了解，为了让大家认识和更进一步的了解，小编给大家总结了以下内容，希望这篇“SQL Server中自定义分割月的方法及SQL代码是什么”文章能对大家有帮助。

在最近的项目开发过程中，遇到了Sql server自动分割月的功能需求，这里在网上整理下资料.

1、为何出现自定义分割月的需求

今天梳理一个平台的所有函数时，发现了一个自定义分割月函数，也就是指定分割月的开始日索引值（可以从1-31闭区间内的任何一个值）来获取指定日期所对应的分割月数值。这个函数当时是为了解决业务部门获取非标准月（标准月就是从每个月的第一天到最后一天组成一个完成的标准月份）的统计汇总数据的。例如：如果指定分割月的开始日索引值为5则表示某个月的5号到下个月的4号之间作为一个完整的分割月；同样地如果指定分割月的开始日索引值为1则表示标准月等等。

我仔细梳理了这个函数进行了重构简化以及扩展，该自定义分割月函数的实现区别之前写的SQL Server时间粒度系列----第3节旬、月时间粒度详解文章中将一个整数值和月份日期相互转换功能，这个是按照标准月来实现的，虽然思路大致相同，但是并没有针对之前的月份日期和整数值转换函数对来进行扩展而是独立开发新的功能函数。也是为了尽量做到函数功能职责单一性、稳定性、可维护性以及可扩展性。

2、sql server实现自定义分割月功能

自定义分割月功能函数包括两个标量函数：ufn_SegMonths和ufn_SegMonth2Date。ufn_SegMonths获取指定的日期在自定义分割月对应的分割月数值；ufn_SegMonth2Date获取指定一个分割月数值赌对应的月份日期。

sql server 版本的实现T-SQL代码如下：

IF OBJECT_ID(N'[dbo].[ufn_SegMonths]', 'FN') IS NOT NULL

BEGIN

  DROP FUNCTION [dbo].[ufn_SegMonths];

END

GO

--==================================

-- 功能：根据自定义月开始索引值获取指定日期所在的自定义月数。

-- 说明：自定义分割月数 = 年整数值*100 + 当前所在分割月值。

-- 环境：SQL Server 2005+。

-- 调用：SET @intSegMonths = dbo.fn_SegMonths('2008-01-14', 15)。

-- 创建：XXXX-XX-XX XX:XX-XX:XX XXX 创建函数实现。

-- 修改：XXXX-XX-XX XX:XX-XX:XX XXX XXXXXXXX。

--==================================

CREATE FUNCTION [dbo].[ufn_SegMonths]

(

   @dtmDate AS DATETIME            -- 日期

  ,@tntSegStartIndexOfMonth AS INT = 15    -- 自定义分割月开始索引值（1-31)

)

RETURNS INT

AS

BEGIN

  IF (@tntSegStartIndexOfMonth = 0 OR @tntSegStartIndexOfMonth >= 32)

  BEGIN

    SET @tntSegStartIndexOfMonth = 15;

  END

  DECLARE

     @intYears AS INT

    ,@tntMonth AS TINYINT

    ,@sntDay AS SMALLINT;

  SELECT

     @intYears = DATEDIFF(YEAR, '1900-01-01', @dtmDate)

    ,@tntMonth = DATEPART(MONTH, @dtmDate)

    ,@sntDay = DATEPART(DAY, @dtmDate);

  IF (@sntDay >= @tntSegStartIndexOfMonth)

  BEGIN

    SET @tntMonth = @tntMonth + 1;

  END

  IF (@tntMonth > 12)

  BEGIN

    SELECT

       @intYears = @intYears + 1

      ,@tntMonth = @tntMonth - 12;

  END

  RETURN @intYears * 100 + @tntMonth;

END

GO

IF OBJECT_ID(N'[dbo].[ufn_SegMonths2Date]', 'FN') IS NOT NULL

BEGIN

  DROP FUNCTION [dbo].[ufn_SegMonths2Date];

END

GO

--==================================

-- 功能：获取自定义分割月数对应的自定义分割月日期。

-- 说明：自定义分割月日期 = 自定义分割月数/100对应的年整数日期“组合”当前所在分割月值。

-- 环境：SQL Server 2005+。

-- 调用：SET @dtmSegMonthDate = dbo.fn_SegMonths2Date(11602)。

-- 创建：XXXX-XX-XX XX:XX-XX:XX XXX 创建函数实现。

-- 修改：XXXX-XX-XX XX:XX-XX:XX XXX XXXXXXXX。;

--==================================

CREATE FUNCTION [dbo].[ufn_SegMonths2Date]

(

   @intSegMonths AS INT            -- 自定义分割月数

)

RETURNS DATETIME

AS

BEGIN

  DECLARE @dtmDefaultBasedate AS DATETIME;

  SET @dtmDefaultBasedate = '1900-01-01';

  IF ((@intSegMonths IS NULL) OR (@intSegMonths <= 0))

  BEGIN

    RETURN @dtmDefaultBasedate;

  END

  DECLARE

     @intYears AS INT

    ,@intMonth AS INT;

  SELECT

     @intYears = @intSegMonths / 100

    ,@intMonth = @intSegMonths % 100;

  RETURN DATEADD(MONTH, @intMonth - 1, DATEADD(YEAR, @intYears, @dtmDefaultBasedate));

END

GO

3、测试验证效果

针对以上简单的测试代码如下：

DECLARE

   @dtmStartDate AS DATETIME

  ,@dtmEndDate AS DATETIME;

SELECT

   @dtmStartDate = '2000-01-01'

  ,@dtmEndDate = '2016-12-31';

SELECT

  [T1].*

  ,[dbo].[ufn_SegMonths2Date]([T1].[SegMonths]) AS SegMonthDate

FROM (

  SELECT

    [T].[CDate]

    ,[dbo].[ufn_SegMonths]([T].[CDate], 28) AS SegMonths

  FROM (

    SELECT

      DATEADD(DAY, [Num], @dtmStartDate) AS CDate

    FROM

      [dbo].[ufn_GetNums](0, DATEDIFF(DAY, @dtmStartDate, @dtmEndDate))

  ) AS T

  WHERE [T].[CDate] BETWEEN '2014-12-01' AND '2016-03-31'

) AS T1

WHERE DATEPART(DAY, [T1].[CDate]) >= 27

GO

注意：以上测试代码使用了SQL Server数字辅助表的实现这边文章的内联表值函数ufn_GetNums。

（编辑：聊城站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!