SQL Server时间粒度系列----第5节小时、分钟时间粒度详解
本文目录列表:
这里说的时间粒度是指带有小时时间部分的日期时间,这个日期时间精确度是小时的。提供将带小时的日期时间和整数相互转换的功能,和以前日、周、旬、季、年那样。
实现带小时的日期时间和整数相互转换的功能函数,T-SQL如下:
1 IF OBJECT_ID(N'dbo.ufn_Hours', 'FN') IS NOT NULL 2 BEGIN 3 DROP FUNCTION dbo.ufn_Hours; 4 END 5 GO 6 7 --================================== 8 -- 功能: 获得指定的日期时间基于基准日期的总小时数(一个整数值) 9 -- 说明: 如果指定的日期时间为NULL或者小于基准日期“1900-01-01”时,则其值默认基准日期 10 -- 结果值为非负整数,从0开始计数。 11 -- 作者: XXX 12 -- 创建: yyyy-MM-dd 13 -- 修改: yyyy-MM-dd XXX 修改内容描述 14 -- 调用: SET @intHours = dbo.ufn_Hours('2008-01-14 17:45') -- 947033 15 --================================== 16 CREATE FUNCTION dbo.ufn_Hours 17 ( 18 @dtmDate DATETIME 19 ) RETURNS INT 20 --$Encode$-- 21 AS 22 BEGIN 23 SET @dtmDate = dbo.ufn_GetValidDate(@dtmDate); 24 25 -- datepart参数也可以为hh 26 RETURN DATEDIFF(HOUR, '1900-01-01', @dtmDate) 27 END 28 GO 29 30 IF OBJECT_ID(N'dbo.ufn_Hours2Date', 'FN') IS NOT NULL 31 BEGIN 32 DROP FUNCTION dbo.ufn_Hours2Date; 33 END 34 GO 35 36 --================================== 37 -- 功能: 获得一个整数值基于基准日期对应的日期时间 38 -- 说明: 如果指定的整数值为NULL或为负整数时,则其值默认为0; 39 -- 如果指定的整数值大于“9999-12-31 23:00:00”对应的整数值时,则其值默认设置为“9999-12-31 23:00:00”对应的整数值 40 -- 结果值为从基准日期开始计数的日期 41 -- 作者: XXX 42 -- 创建: yyyy-MM-dd 43 -- 修改: yyyy-MM-dd XXX 修改内容描述 44 -- 调用: SET @dtmDate = dbo.fn_Hours2Date(947033) -- '2008-01-14 17:00' 45 --================================== 46 CREATE FUNCTION dbo.ufn_Hours2Date 47 ( 48 @intHours INT 49 ) RETURNS DATETIME 50 AS 51 BEGIN 52 SET @intHours = dbo.ufn_GetValidDateNum(@intHours); 53 54 DECLARE @intHoursMax AS INT; 55 SET @intHoursMax = dbo.ufn_Hours('9999-12-31 23:00:00'); 56 57 IF @intHours >= @intHoursMax 58 BEGIN 59 SET @intHours = @intHoursMax; 60 END 61 62 -- datepart参数也可以为hh 63 RETURN DATEADD(hh, @intHours, '1900-01-01') 64 END 65 GO
测试以上功能函数的效果,T-SQL如下:
1 DECLARE @dtmDate AS DATETIME; 2 SET @dtmDate = '2008-01-14 17:00:00'; 3 4 SELECT @dtmDate AS 'The Current DateTime (Precision:Hour)' 5 ,dbo.ufn_Hours(@dtmDate) AS 'The Total Of Hours Base-on Basedate "1900-01-01"' 6 ,dbo.ufn_Hours2Date(dbo.ufn_Hours(@dtmDate))AS 'Hours Basedatetime Mapping'; 7 GO
执行后的查询结果如下图:
![](https://images2015.cnblogs.com/blog/82100/201601/82100-20160112125329960-1023096610.png)
同小时时间粒度相似,这个粒度是带有分钟时间部分的日期时间,其精确度是分钟的。不过使用datediff(minute, '1900-01-01', @dtmDateTime)时,@dtmDateTime最大只能为“5983-01-24 02:07:00”,也就是datediff(minute, @starddatetime, @enddatetime)的结果值超过int的最大值(2^15 - 1),该函数就会错的。如下图所示的错误:
实现带分钟的日期时间和整数相互转换的功能函数,T-SQL代码如下:
1 IF OBJECT_ID(N'dbo.ufn_Minutes', 'FN') IS NOT NULL 2 BEGIN 3 DROP FUNCTION dbo.ufn_Minutes; 4 END 5 GO 6 7 --================================== 8 -- 功能: 获得指定的日期时间基于基准日期的总小时数(一个整数值) 9 -- 说明: 如果指定的日期时间为NULL或者小于基准日期“1900-01-01”时,则其值默认基准日期 10 -- 结果值为非负整数,从0开始计数。 11 -- 作者: XXX 12 -- 创建: yyyy-MM-dd 13 -- 修改: yyyy-MM-dd XXX 修改内容描述 14 -- 调用: SET @intMinutes = dbo.ufn_Minutes('2008-01-14 17:10:00'); 15 --================================== 16 CREATE FUNCTION dbo.ufn_Minutes 17 ( 18 @dtmDate DATETIME 19 ) RETURNS INT 20 AS 21 BEGIN 22 SET @dtmDate = dbo.ufn_GetValidDate(@dtmDate); 23 24 -- datepart参数也可以是mi或n 25 RETURN DATEDIFF(MINUTE, '1900-01-01', @dtmDate) 26 END 27 GO 28 29 IF OBJECT_ID(N'dbo.ufn_Minutes2Date', 'FN') IS NOT NULL 30 BEGIN 31 DROP FUNCTION dbo.ufn_Minutes2Date; 32 END 33 GO 34 35 --================================== 36 -- 功能: 获得一个整数值基于基准日期对应的日期时间 37 -- 说明: 如果指定的整数值为NULL或为负整数时,则其值默认为0; 38 -- 如果指定的整数值大于“5983-01-24 02:07:00”对应的整数值时,则其值默认设置为“5983-01-24 02:07:00”对应的整数值 39 -- 结果值为从基准日期开始计数的日期 40 -- 作者: XXX 41 -- 创建: yyyy-MM-dd 42 -- 修改: yyyy-MM-dd XXX 修改内容描述 43 -- 调用: SET @dtmDate = dbo.ufn_Minutes2Date(56821990) -- '2008-01-14 17:10:00' 44 --================================== 45 CREATE FUNCTION dbo.ufn_Minutes2Date 46 ( 47 @intMinutes INT 48 ) RETURNS DATETIME 49 AS 50 BEGIN 51 SET @intMinutes = dbo.ufn_GetValidDateNum(@intMinutes); 52 53 DECLARE @intMinutesMax AS INT; 54 SET @intMinutesMax = dbo.ufn_Minutes('5983-01-24 02:07:00'); 55 56 IF @intMinutes >= @intMinutesMax 57 BEGIN 58 SET @intMinutes = @intMinutesMax; 59 END 60 61 -- datepart参数也可以是mi或n 62 RETURN DATEADD(MINUTE, @intMinutes, '1900-01-01') 63 END 64 GO
测试以上功能函数的效果,T-SQL如下:
1 DECLARE @dtmDate AS DATETIME; 2 SET @dtmDate = '2008-01-14 17:10:00'; 3 SELECT @dtmDate AS 'The Current DateTime (Precision:Minute)' 4 ,dbo.ufn_Minutes(@dtmDate) AS 'The Total Of Minutes Base-on Basedate "1900-01-01"' 5 ,dbo.ufn_Minutes2Date(dbo.ufn_Minutes(@dtmDate)) AS 'Minute Basedatetime Mapping' 6 GO
执行后的查询结果如下图:
![](https://images2015.cnblogs.com/blog/82100/201601/82100-20160112125318335-1340744385.png)
注意:分钟时间粒度仅仅提供的参考是实现,因为datediff(minute,@dtmStartDate,@dtmEndDate)返回结果值是int数据类型,受int最大值(2^15-1)的限制,建议谨慎使用。
本文我们学习了带时间部分(小时、分钟)的日期时间和整数相互转换的功能函数,也指出了带分钟的转换为整数的限制。
1、https://msdn.microsoft.com/zh-cn/library/ms186819(v=sql.90).aspx