你的位置:首页 > 数据库

[数据库]模拟实现SQL Server中的datepart(week,date)的功能


本文目录列表:
1、为什么要模拟实现datepart(week,date)的功能
2、具体实现思路
3、T-SQL代码实现逻辑
4、总结语
5、参考清单列表
 
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 BEGIN100   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 END105 GO106 107 IF OBJECT_ID(N'dbo.ufn_WeekOfYear', 'FN') IS NOT NULL108 BEGIN109   DROP FUNCTION dbo.ufn_WeekOfYear;110 END111 GO112 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_WeekOfYear121 (122   @dtmDate AS DATETIME      --指定日期123   ,@tntDateFirst AS TINYINT = 1  --第一天日期(从1、2、3、……、7分别对应周一、周二、周三、……、周日)    124 ) RETURNS TINYINT125 AS126 BEGIN127   DECLARE @tntWeekOfYear AS TINYINT;128   SET @tntWeekOfYear = 1;129 130   DECLARE 131     @dtmFirstDayOfYear AS DATETIME132     ,@dtmFirstWeekFirstDayOfYear AS DATETIME;133   SELECT134     @dtmFirstDayOfYear = dbo.[ufn_FirstDayOfYear](@dtmDate)  135     ,@dtmFirstWeekFirstDayOfYear = dbo.[ufn_FirstWeekFirstDayOfYear](@dtmDate, @tntDateFirst);  136 137   IF NOT(@dtmDate >= @dtmFirstDayOfYear AND @dtmDate < @dtmFirstWeekFirstDayOfYear)138   BEGIN139     SET @tntWeekOfYear = @tntWeekOfYear + DATEDIFF(DAY, @dtmFirstWeekFirstDayOfYear, @dtmDate) / 7; 140 141     IF @dtmFirstDayOfYear < @dtmFirstWeekFirstDayOfYear142     BEGIN143       SET @tntWeekOfYear = @tntWeekOfYear + 1;144     END      145   END146 147   RETURN @tntWeekOfYear;148 END149 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 BEGIN13   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 DayCountOfWeek20    ,T2.[DefaultDayOfWeek]21    ,@@DATEFIRST AS [DefaultFirstDateOfWeek]22    ,[T2].[DefaultWeekOfYear]23    ,COUNT(T2.[FullDate]) OVER (PARTITION BY YEAR(T2.[FullDate]), T2.[DefaultWeekOfYear]) AS DefaultDayCountOfWeek24   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 FullDate34       FROM [dbo].[ufn_GetNums](0, DATEDIFF(DAY, @dtmStartDate, @dtmEndDate))35     ) AS T36   ) AS T2;37 38   SET @tntDateFirst = @tntDateFirst + 1;39 END 40 GO

 



以上测试效果如下图:




 
4、总结语
 
由于我之前写过几个版本的ufn_WeekOfYear实现,但是通过博文发出去的有两个版本的,直到看到BIWork的SQL Server - 把星期一(周一)当作每个星期的开始在一年中求取周数这篇博文才发现自己之前发布的两个博文中的ufn_WeekOfYear是存在问题的,这才花费了不少时间再次梳理和思考这个功能的实现,都说变成代码需要测试,T-SQL代码一样要严格的单元测试才行,不然真是迷惑了自己,也误导了别人的。
  
5、参考清单列表
5.1、DATEPART (Transact-SQL)
5.2、SQL Server时间粒度系列----第2节日期、周时间粒度详解
5.3、SQL Server时间粒度系列----第7节日历数据表详解
5.4、SQL Server - 把星期一(周一)当作每个星期的开始在一年中求取周数
5.5、SQL Server数字辅助表的实现