No TimeDiff function in T-SQL?

Question

I have created a new column called DesiredTimeOfFileCreation of type time(7); this will indicate at what time the data is to be extracted to an export file.

Let's suppose it is set to 6:00:00. I then have a SQL agent job scheduled at 6:00 (probably every 30 minutes), but it might run at 6:00:05 or even 6:01. I want to select all rows where the DesiredTimeOfFileCreation is less than 30 minutes ago.

Does someone already have a user-defined TimeDiff function? Or is there an easy alternative that I'm missing?

Answer 1

This will probably work for me:

 WHERE DATEDIFF(Minute,DesiredFileCreationTimeLocal,cast(GETDATE() as time)) < 30

I had to research what happened if you pass a Time as a variable to the DateDiff function. It seems to work, the only trick is then how to pass two times to it.

My real-world scenario is more complex, because we are dealing with different locations in different time zones, so there will some UTC conversions added to the above.

Answer 2

As Martin mentioned above, I need to handle the midnight wrap-around.

This seems overly complicated. The code below seems to work if one time is one hour before midnight, and one is within an hour after. Would be nice to make it more generic. I think the only way to do that would be to make up a dummy date, which I may experiment with next.

The reason I'm passing a date in the unit test is that I will be passing a casted version of GetUTCDate() as a parm:

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

NOTE: This code shows that you cannot go past 24 hours in a time; it just wraps back around (with no error!):

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

Improved version, using an arbitrary date.

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

This is how I will use the function. We store the local time that an airport wants an extract file in a table. Then we will use SQL agent or BizTalk to poll every 30 minutes looking for work to do. AirportCode is a column in the table, and we have our own crazy function that converts for timezones.

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