模拟实现SQL Server中的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,下同),请继续往下看。
由于知道了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代码实现。
根据以上具体的思路,根据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
以上测试效果如下图:
由于我之前写过几个版本的ufn_WeekOfYear实现,但是通过博文发出去的有两个版本的,直到看到BIWork的SQL Server - 把星期一(周一)当作每个星期的开始在一年中求取周数这篇博文才发现自己之前发布的两个博文中的ufn_WeekOfYear是存在问题的,这才花费了不少时间再次梳理和思考这个功能的实现,都说变成代码需要测试,T-SQL代码一样要严格的单元测试才行,不然真是迷惑了自己,也误导了别人的。