Sql Server TimeSpan

With those functions in place, we can add two more that will give us further flexibility when working with dates and times: The concept of a "TimeSpan", very similar to what is available in the .NET framework.

create function TimeSpan(@Days int, @Hours int, @Minutes int, @Seconds int)
-- returns a datetime the specified # of days/hours/minutes/seconds from the "base" date of 1/1/1900 (a "TimeSpan")
returns datetime
as
begin
return dbo.Time(@Hours,@Minutes,@Seconds) + @Days
end

create function TimeSpanUnits(@Unit char(1), @TimeSpan datetime)
-- returns the # of units specified in the TimeSpan.
-- The Unit parameter can be: "d" = days, "h" = hours, "m" = minutes, "s" = seconds
returns int
as
begin
return case @Unit
when 'd' then datediff(day, 0, @TimeSpan)
when 'h' then datediff(hour, 0, @TimeSpan)
when 'm' then datediff(minute, 0, @TimeSpan)
when 's' then datediff(second, 0, @TimeSpan)
else Null end
end

Here, a TimeSpan is just a datetime offset from the "base" date of 1/1/1900. Creating one is the same as creating a Time using the Time() function, but we have added a parameter for Days to give more flexibility.

The TimeSpanUnits() function works similar to standard T-SQL DatePart() function, but it returns the total # of units in the given time span. So, if you create a time span of 1 day and 2 hours, then TimeSpanUnits("d") will return 1 and TimeSpanUnits("h") will return 26. Negative values can be returned as well. You also may wish to implement the TimeSpanUnits() function as multiple functions, one per unit (e.g., TimeSpanHours(), TimeSpanDays(), etc) depending on your preference.

Of course, a simple way to create a TimeSpan is to simply subtract two standard T-SQL DateTimes. Also please note that we can add and subtract Dates, Times, and TimeSpans all together using standard + and - operators and everything will work as expected. We can also add integers to our Dates and Times which will add entire days to the values.

Here's a TimeSpan usage example:

declare @Deadline datetime -- remember, we still use standard datetimes for everything, include TimeSpans
set @Deadline = dbo.TimeSpan(2,0,0,0) -- the deadline is two days

declare @CreateDate datetime
declare @ResponseDate datetime

set @CreateDate = dbo.DateTime(2006,1,3,8,30,0) -- Jan 3, 2006, 8:30 AM
set @ResponseDate = getdate() -- today

-- See if the response date is past the deadline:
select case when @ResponseDate > @CreateDate + @Deadline then 'overdue.' else 'on time.' end as Result

-- Find out how many total hours it took to respond:
declare @TimeToRepond datetime
set @TimeToRespond = @ResponseDate - @CreateDate

select dbo.TimeSpanUnits('h', @TimeToRespond) as ResponseTotalHours

-- Return the response time as # of days, # of hours, # of minutes:
select dbo.TimeSpanUnits('d',@TimeToRespond) as Days, DatePart(hour, @TimeToRespond) as Hours, DatePart(minute, @TimeToRespond) as Minutes

-- Return two days and two hours from now:
select getdate() + dbo.TimeSpan(2,2,0,0)