T-SQL中没有TimeDiff函数吗？ [英] No TimeDiff function in T-SQL?

问题描述

我已经创建了一个名为DesiredTimeOfFileCreation的新列，类型为time(7)；这将指示将数据提取到导出文件的时间。

假设设置为6：00：00。然后，我将一个SQL代理作业安排在6：00(可能每30分钟)，但它可能在6：00：05甚至6：01运行。我要选择DesiredTimeOfFileCreation小于30分钟的所有行。

是否有人已经拥有用户定义的TimeDiff函数？或者，有没有我错过的简单的替代方案？

推荐答案

如上所述，我需要处理午夜总结。

这似乎过于复杂。如果一次是午夜前一小时，而另一次是午夜后一小时，下面的代码似乎是有效的。如果能让它更通俗一些就好了。我认为要做到这一点，唯一的方法就是编造一个虚拟的约会对象，我下一步可能会尝试这样做。

我在单元测试中传递日期的原因是我将传递GetUTCDate()的强制转换版本作为参数：

ALTER FUNCTION TimeDiffMinutes
(
    @FirstTime time(7), 
    @SecondTime time(7)

)
RETURNS int 
AS
BEGIN

/* 

Unit Test: 
   select dbo.TimeDiffMinutes('13:31',cast ('2013-06-10 13:35' as time)),  -- simple test 
          dbo.TimeDiffMinutes('23:55',cast ('2013-06-10 00:05' as time))   -- test midnight wrap-around 

   select dbo.TimeDiffMinutes('23:55',cast ('2013-06-10 00:05' as time))   -- test midnight wrap-around 

*/ 

    -- Declare the return variable here
    DECLARE @resultMinutes int 
    DECLARE @Hour int 

    -- although we can compare two times, the problem is that if one time is 11:55 and the other is 00:05, we want to show 10 minutes difference. 
    -- We cannot add 24 hours to a time, because that would be an invalid value 

    Set @Hour = datePart(hour,@SecondTime) 

    if (@Hour <= 0) 
      begin 
         -- increase both times by an hour so we can compare them, 23:55 will wrap around to 01:55
         Set @FirstTime = DateAdd(hour,+1,@FirstTime)
         Set @SecondTime = DateAdd(hour,+1,@SecondTime)
      end 

    SET @resultMinutes = DATEDIFF(Minute,@FirstTime,@SecondTime)                   


    -- Return the result of the function
    RETURN @resultMinutes 


END

注意：此代码显示您一次不能超过24小时；它只是回绕(没有错误！)：

declare @FirstTime time(7)
SET @FirstTime = '23:05' 
print @FirstTime
Set @FirstTime = DATEADD(HOUR,1,@FirstTime)
print @FirstTime

改进版本，使用任意日期。

ALTER FUNCTION TimeDiffMinutes
(
    @FirstTime time(7), 
    @SecondTime time(7)

)
RETURNS int 
AS
BEGIN

/* 

Unit Test: 
   select dbo.TimeDiffMinutes('13:31',cast ('2013-06-10 13:35' as time)),  -- simple test 
          dbo.TimeDiffMinutes('23:55',cast ('2013-06-10 00:05' as time))   -- test midnight wrap-around 

   select dbo.TimeDiffMinutes('23:55',cast ('2013-06-10 00:05' as time))   -- test midnight wrap-around 

*/ 

    -- Declare the return variable here
    DECLARE @resultMinutes int 
    DECLARE @Hour int 
    DECLARE @FirstDate datetime 
    DECLARE @SecondDate datetime

    Set @FirstDate  = CAST('2001-01-01 ' + Convert(varchar(12),@FirstTime) as DateTime)
    Set @SecondDate = CAST('2001-01-01 ' + Convert(varchar(12),@SecondTime) as DateTime)


    -- although we can compare two times, the problem is that if one time is 11:55 and the other is 00:05, we want to show 10 minutes difference. 
    -- We cannot add 24 hours to a time, because that would be an invalid value 

    Set @Hour = datePart(hour,@SecondDate) 

    if (@Hour <= 0) 
      begin 
         -- increase both times by an hour so we can compare them, 23:55 will wrap around to 01:55
         Set @SecondDate = DateAdd(day,+1,@SecondDate)
      end 

    SET @resultMinutes = DATEDIFF(Minute,@FirstDate,@SecondDate)                   


    -- Return the result of the function
    RETURN @resultMinutes 


END

这就是我将如何使用该函数。我们将机场需要提取文件的本地时间存储在一个表中。然后我们将使用SQL代理或BizTalk每30分钟轮询一次，寻找要做的工作。AirportCode是表中的一列，我们有自己的用于转换时区的疯狂函数。

select *, 
       dbo.TimeDiffMinutes(
                DesiredFileCreationTimeLocal,
                cast(dbo.LocationLocalTimeFromAirportCode(AirportCode,GETUTCDATE()) as time)
                ) as 'MinutesAgo'
from TransactionExtractDistribution 
where    dbo.TimeDiffMinutes(
                DesiredFileCreationTimeLocal,
                cast(dbo.LocationLocalTimeFromAirportCode(AirportCode,GETUTCDATE()) AS time)
                ) < 30 

这篇关于T-SQL中没有TimeDiff函数吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！