模拟实现SQL Server中的datepart(week,date)的功能

本文目录列表：
 
1、为什么要模拟实现datepart(week,date)的功能
 
在SQL Server时间粒度系列----第2节日期、周时间粒度详解这篇博文中，就有个函数ufn_WeekOfYear----就是用了datepart(week,date)来实现获取，不过该函数是依赖@@datefirst这个全局变量值的，SQL Server 默认这个值时7（美国的习惯，周日作为一周的第一天），而我们中国则是周一作为一周的第一天的。后来在SQL Server时间粒度系列----第7节日历数据表详解这篇博文中，依然重新实现了ufn_WeekOfYear，当时没有充分的测试，现在发现也是存在bug的。后来又看到BIWork的这篇博文----SQL Server - 把星期一(周一)当作每个星期的开始在一年中求取周数，这才思考许久才重新实现类似datepart(week,date)的功能函数。
 
SQL Server提供的datepart(week,date)这个函数是依赖@@datefirst这个全局变量值的，由于不同的区域每周的第一天有所不同的，但是@@datefirst不能再函数中重新修改值（其实通过set datefirst num来修改的），这个是很大的不方面的，为了灵活地获取指定日期所在当前年的星期索引数值(索引数值从1开始计数，依次为1、2、……、51、52、53、54，下同），请继续往下看。
 
 
2、具体实现思路
 
由于知道了datepart(week,date)和@@datefirst的依赖关系以及其存在的不方便，将@@datefirst的值以一个参数的形式出现，这样就可以动态的设定一周的第一天啦。再加上指定的日期以及一周第一天索引数值（索引数值从1开始计数，依次为1、2、……、7，分别对应周一、周二、……、周日，例如：一周第一天索引值为1，即周一是一周的第一天）这两个参数来实现模拟datepart(week,date)函数的功能，具体思路大致分为如下步骤：
1)、获取指定日期所在当前年的第一天。
2)、获取指定日期所在当前周的日索引数值（索引数值从1开始计数，依次为1、2、……、7，分别对应周一、周二、……、周日）。
3)、获取指定日期和一周第一天索引值来获得当前年的第一周的第一天。
4）、指定日期、当前年的第一天和当前年的第一周的第一天这三个日期进行逻辑判断如下：
4.1)、指定日期大于等于当前年的第一天且小于当前年的第一周的第一天时，当前年的星期索引数值为1。
4.2)、4.1的否定为逻辑真时，先通过当前年的星期索引数值默认值为1在加上当前年的第一天与当前的第一周的第一天的日期天差除以7的值，然后通过当前年的第一天小于当前年的第一周的第一天时，将上边的结果值在加上1，否则上边的结果值就是当前年的星期索引数值。
 
可能以上文字表述具体的思路有些不太清楚，那就请继续看下面的T-SQL代码实现。
 
3、T-SQL代码实现逻辑以及测试效果
 
根据以上具体的思路，根据T-SQL语言来进行一种实现，T-SQL代码实现路基如下：
  1 IF OBJECT_ID(N'dbo.ufn_FirstDayOfYear', 'FN') IS NOT NULL
  2 BEGIN
  3     DROP FUNCTION dbo.ufn_FirstDayOfYear;
  4 END
  5 GO
  6  
  7 --==================================
  8 -- 功能：获取指定日期所在当前年的第一天
  9 -- 说明：具体功能说明
 10 -- 兼容：运行SQL Server 2005+
 11 -- 创建：2016-07-06 09:00-09:05 剑走江湖 创建实现
 12 -- 修改：yyyy-MM-dd hh:mm-hh:mm XXX 修改内容描述
 13 --==================================
 14 CREATE FUNCTION dbo.ufn_FirstDayOfYear
 15 (
 16     @dtmDate AS DATETIME    --指定日期
 17 ) RETURNS DATETIME
 18 AS
 19 BEGIN
 20     RETURN DATEADD(YEAR, DATEDIFF(YEAR, 0, @dtmDate), 0);
 21 END
 22 GO
 23  
 24 IF OBJECT_ID(N'dbo.ufn_FirstWeekFirstDayOfYear', 'FN') IS NOT NULL
 25 BEGIN
 26     DROP FUNCTION dbo.ufn_FirstWeekFirstDayOfYear;
 27 END
 28 GO
 29  
 30 --==================================
 31 -- 功能：获取指定日期所在当前年的第一周的第一天
 32 -- 说明：可以通过参数@tntDateFirst动态指定一周开始的第一天，类似全局变量@@DATEFIRST的值且保持一致
 33 -- 兼容：运行SQL Server 2005+
 34 -- 创建：2016-07-06 09:05-09:15 剑走江湖 创建实现
 35 -- 修改：yyyy-MM-dd hh:mm-hh:mm XXX 修改内容描述
 36 --==================================
 37 CREATE FUNCTION dbo.ufn_FirstWeekFirstDayOfYear
 38 (
 39     @dtmDate AS DATETIME                --指定日期
 40     ,@tntDateFirst AS TINYINT = 1        --第一天日期（从1、2、3、……、7分别对应周一、周二、周三、……、周日)
 41 ) RETURNS DATETIME
 42 AS
 43 BEGIN
 44     IF (@tntDateFirst IS NULL OR @tntDateFirst = 0 OR @tntDateFirst NOT BETWEEN 1 AND 7)
 45     BEGIN
 46         SET @tntDateFirst = 1;
 47     END
 48  
 49     DECLARE @dtmFirstWeekFirstDayOfYear AS DATETIME;
 50     SET @dtmFirstWeekFirstDayOfYear = 0;
 51  
 52     DECLARE
 53          @dtmFirstDayOfYear AS DATETIME
 54         ,@dtmStartDate AS DATETIME        
 55         ,@dtmEndDate AS DATETIME;
 56     SELECT
 57          @dtmFirstDayOfYear = [dbo].[ufn_FirstDayOfYear](@dtmDate)
 58         ,@dtmStartDate = @dtmFirstDayOfYear
 59         ,@dtmEndDate = DATEADD(DAY, 7, @dtmStartDate);
 60  
 61     WHILE (@dtmStartDate <= @dtmEndDate)
 62     BEGIN
 63         IF ([dbo].[ufn_DayOfWeek](@dtmStartDate) = @tntDateFirst)
 64         BEGIN
 65             SET @dtmFirstWeekFirstDayOfYear = @dtmStartDate;
 66  
 67             BREAK;
 68         END
 69  
 70         SET @dtmStartDate = DATEADD(DAY, 1, @dtmStartDate);
 71     END    
 72  
 73     RETURN @dtmFirstWeekFirstDayOfYear;
 74 END
 75 GO
 76  
 77 IF OBJECT_ID(N'dbo.ufn_DayOfWeek', 'FN') IS NOT NULL
 78 BEGIN
 79     DROP FUNCTION dbo.ufn_DayOfWeek;
 80 END
 81 GO
 82  
 83 --==================================
 84 -- 功能: 获取指定日期时间的所在当前周的日索引值（索引值从1开始计数，依次为1、2、……、7）
 85 -- 说明: 运行在SQL Server 2005+。
 86 --       结果值从1到7，分别对应从周一到周日，该值与@@DATEFISRT配置函数值保持一致。
 87 --       使用(@@datefirst + datepart(weekday, @dtmDate))%7的结果值从2、3、4、5、6、0、1
 88 --       分别对应周一、周二、周三、周四、周五、周六、周日。
 89 -- 兼容：运行SQL Server 2005+
 90 -- 创建：2016-01-02 hh:mm-hh:mm 剑走江湖 创建实现
 91 -- 修改：yyyy-MM-dd hh:mm-hh:mm XXX 修改内容描述
 92 -- 调用： SELECT dbo.ufn_DayOfWeek('2017-01-07') -- 4（表示星期四）
 93 --==================================
 94 CREATE FUNCTION dbo.ufn_DayOfWeek
 95 (
 96     @dtmDate AS DATETIME                -- 指定的日期时间
 97 ) RETURNS TINYINT
 98     --$Encode$--
 99 BEGIN
100     DECLARE @tntRemainder AS TINYINT;
101     SET @tntRemainder = (@@DATEFIRST + DATEPART(WEEKDAY, @dtmDate)) % 7;   
102  
103     RETURN (CASE WHEN @tntRemainder <= 1 THEN @tntRemainder + 6 ELSE @tntRemainder - 1 END);
104 END
105 GO
106  
107 IF OBJECT_ID(N'dbo.ufn_WeekOfYear', 'FN') IS NOT NULL
108 BEGIN
109     DROP FUNCTION dbo.ufn_WeekOfYear;
110 END
111 GO
112  
113 --==================================
114 -- 功能：获取指定日期所在当前年的星期索引值(索引值从1开始计数，依次为1、2、……、51、52、53、54）
115 -- 说明：具体功能说明
116 -- 兼容：运行SQL Server 2005+
117 -- 创建：2016-07-06 09:15-09:35 剑走江湖 创建实现
118 -- 修改：yyyy-MM-dd hh:mm-hh:mm XXX 修改内容描述
119 --==================================
120 CREATE FUNCTION dbo.ufn_WeekOfYear
121 (
122     @dtmDate AS DATETIME            --指定日期
123     ,@tntDateFirst AS TINYINT = 1    --第一天日期（从1、2、3、……、7分别对应周一、周二、周三、……、周日)        
124 ) RETURNS TINYINT
125 AS
126 BEGIN
127     DECLARE @tntWeekOfYear AS TINYINT;
128     SET @tntWeekOfYear = 1;
129  
130     DECLARE 
131          @dtmFirstDayOfYear AS DATETIME
132         ,@dtmFirstWeekFirstDayOfYear AS DATETIME;
133     SELECT
134          @dtmFirstDayOfYear = dbo.[ufn_FirstDayOfYear](@dtmDate)    
135         ,@dtmFirstWeekFirstDayOfYear = dbo.[ufn_FirstWeekFirstDayOfYear](@dtmDate, @tntDateFirst);    
136  
137     IF NOT(@dtmDate >= @dtmFirstDayOfYear AND @dtmDate < @dtmFirstWeekFirstDayOfYear)
138     BEGIN
139         SET @tntWeekOfYear =  @tntWeekOfYear +  DATEDIFF(DAY, @dtmFirstWeekFirstDayOfYear, @dtmDate) / 7; 
140  
141         IF @dtmFirstDayOfYear < @dtmFirstWeekFirstDayOfYear
142         BEGIN
143             SET @tntWeekOfYear = @tntWeekOfYear + 1;
144         END            
145     END
146  
147     RETURN @tntWeekOfYear;
148 END
149 GO

 

测试代码如下：
 1 DECLARE
 2      @tntDateFirst    AS TINYINT
 3     ,@tntMaxDateFirst AS TINYINT
 4     ,@dtmStartDate    AS DATETIME
 5     ,@dtmEndDate      AS DATETIME;
 6 SELECT
 7      @tntDateFirst    = 1
 8     ,@tntMaxDateFirst = 7
 9     ,@dtmStartDate    = '2000-01-01'
10     ,@dtmEndDate      = '2000-01-07';
11 WHILE (@tntDateFirst <= @tntMaxDateFirst)
12 BEGIN
13     SELECT 
14         [T2].[FullDate]
15        ,[T2].[DayOfWeek]
16        ,[T2].[FirstWeekFirstDayOfYear]
17        ,@tntDateFirst AS [FirstDateOfWeek]
18        ,[T2].[WeekOfYear]
19        ,COUNT(T2.[FullDate]) OVER (PARTITION BY YEAR(T2.[FullDate]), T2.[WeekOfYear]) AS DayCountOfWeek
20        ,T2.[DefaultDayOfWeek]
21        ,@@DATEFIRST AS [DefaultFirstDateOfWeek]
22        ,[T2].[DefaultWeekOfYear]
23        ,COUNT(T2.[FullDate]) OVER (PARTITION BY YEAR(T2.[FullDate]), T2.[DefaultWeekOfYear]) AS DefaultDayCountOfWeek
24     FROM (
25         SELECT 
26              T.[FullDate]
27             ,[dbo].[ufn_DayOfWeek](T.[FullDate]) AS [DayOfWeek]
28             ,[dbo].[ufn_FirstWeekFirstDayOfYear](T.[FullDate], @tntDateFirst) AS [FirstWeekFirstDayOfYear]        
29             ,[dbo].[ufn_WeekOfYear](T.[FullDate], @tntDateFirst) AS [WeekOfYear]
30             ,DATEPART(WEEKDAY, T.[FullDate]) AS [DefaultDayOfWeek]
31             ,DATEPART(WEEK, T.[FullDate]) AS [DefaultWeekOfYear]
32         FROM (
33             SELECT DATEADD(DAY, [Num], @dtmStartDate) AS FullDate
34             FROM [dbo].[ufn_GetNums](0, DATEDIFF(DAY, @dtmStartDate, @dtmEndDate))
35         ) AS T
36     ) AS T2;
37  
38     SET @tntDateFirst = @tntDateFirst + 1;
39 END 
40 GO

 

以上测试效果如下图：
 
4、总结语
 
由于我之前写过几个版本的ufn_WeekOfYear实现，但是通过博文发出去的有两个版本的，直到看到BIWork的SQL Server - 把星期一(周一)当作每个星期的开始在一年中求取周数这篇博文才发现自己之前发布的两个博文中的ufn_WeekOfYear是存在问题的，这才花费了不少时间再次梳理和思考这个功能的实现，都说变成代码需要测试，T-SQL代码一样要严格的单元测试才行，不然真是迷惑了自己，也误导了别人的。
  
5、参考清单列表
posted @ 2016-07-06 11:06  剑走江湖  阅读(4308)  评论(2)  编辑  收藏  举报