Calculating timespan with t-sql

Solution 1:

You can get the difference between the two dates to whatever resolution you want (in your example, minutes):

DATEDIFF(minute, @start_date, @end_date)

From there it's a simple matter of dividing minutes into hours and hours into days and modding the remainder.

Solution 2:

I know this thread is older and the original participants are likely no longer watching, but I stumbled upon it, and had already written some code fairly recently to do something very close to what jdiaz is requesting. The result is rendered as a string in D:H:M:S format.

Step one would be to get the time span in seconds:

DECLARE @ElapsedS INT
SET @ElapsedS = DATEDIFF(second, @start_date, @end_date)

Now create the following scalar function:

CREATE FUNCTION [dbo].[udfTimeSpanFromSeconds]
(
    @Seconds int
)
RETURNS varchar(15)
AS
BEGIN
DECLARE 
    --Variable to hold our result
      @DHMS varchar(15)
    --Integers for doing the math
    , @Days int --Integer days
    , @Hours int --Integer hours
    , @Minutes int --Integer minutes
    --Strings for providing the display
    , @sDays varchar(5) --String days
    , @sHours varchar(2) --String hours
    , @sMinutes varchar(2) --String minutes
    , @sSeconds varchar(2) --String seconds

--Get the values using modulos where appropriate
SET @Hours = @Seconds/3600
SET @Minutes = (@Seconds % 3600) /60
SET @Seconds = (@Seconds % 3600) % 60

--If we have 24 or more hours, split the @Hours value into days and hours
IF @Hours > 23 
BEGIN
    SET @Days = @Hours/24
    SET @Hours = (@Hours % 24)
END
ELSE
BEGIN
    SET @Days = 0
END

--Now render the whole thing as string values for display
SET @sDays = convert(varchar, @Days)
SET @sHours = RIGHT('0' + convert(varchar, @Hours), 2)
SET @sMinutes = RIGHT('0' + convert(varchar, @Minutes), 2)
SET @sSeconds = RIGHT('0' + convert(varchar, @Seconds), 2)

--Concatenate, concatenate, concatenate
SET @DHMS =  @sDays + ':' + @sHours + ':' + @sMinutes + ':' + @sSeconds

RETURN @DHMS

END

Now feed your timespan into the newly created function:

SELECT TimeSpan = dbo.udfTimeSpanFromSeconds(@ElapsedS)

Should produce '1:09:19:01'

Solution 3:

CONVERT(varchar,(@end_date-@start_date),108)

This'll give it to you as HH:MM:SS

Cheers

Solution 4:

DATEDIFF can return unintuitive values. For example, the two dates below differ by one second yet DATEDIFF with the parameters below and interpreted as others have interpreted it above returns 1 year:

SELECT DATEDIFF(year, '2005-12-31 23:59:59', '2006-01-01 00:00:00')

Look at the MSDN documentation for DATEDIFF to understand how it works.