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?
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
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.
