T-SQL formatting calculated column as time

Question

Could use guru help on this one. Trying to calculate the time between two datetime values and show as time in a T-SQL query...

SELECT arrivalDate - departDate AS timeToComplete

This should always be less than 24 hours. But who knows what the user may actually input?

I have been trying something like this with no resutls.

SELECT  
   CAST(time(7), 
   CONVERT(datetime, arrivalDate - departDate) AS timeToComplete) AS newTime, 

Instead of showing results as 1:23:41 as an example, is there a way to show results like:

0D, 1H, 23M, 33S. 

Thanks for any guidance on this.

Answer 1

You could get the total difference in seconds and then keep taking the largest part out of that. I.e., start with Days, then hours, minutes and seconds.

DECLARE @arrivalDate DATETIME = '2013-01-19 23:59:59'
DECLARE @departDate DATETIME = '2013-01-25 11:52:30'

DECLARE @SecondsDifference INT = DATEDIFF(SECOND, @arrivalDate, @departDate)

DECLARE @DayDifference INT = @SecondsDifference / 86400
DECLARE @HourDifference INT = (@SecondsDifference - (@DayDifference * 86400)) / 3600
DECLARE @MinDifference INT = (@SecondsDifference - (@DayDifference * 86400) - (@HourDifference * 3600)) / 60
DECLARE @SecDifference INT = (@SecondsDifference - (@DayDifference * 86400) - (@HourDifference * 3600) - (@MinDifference * 60))

I've done it here using variables for clarity, but you could work this into a single query. DATEDIFF wont work for the smaller chunks of the difference until you remove the larger ones because you'd get the totals. For example:

DATEDIFF(HOUR, @arrivalDate, @departDate) 

would return the total number of hours, not the hours less the whole days.

Answer 2

Just to be different :) Try to use this approach:

declare @date1 datetime; declare @date2 datetime;

set @date1 = '2012-05-01 12:00:000'
set @date2 = '2012-05-01 18:00:000'


    SELECT 
    STUFF(
        STUFF(
            STUFF(
                RIGHT(CONVERT(NVARCHAR(19), CONVERT(DATETIME, DATEADD(second, DATEDIFF(S, @date1, @date2), '20000101')), 120), 11), 
                3, 1, 'D, '), 
            8, 1, 'H, '), 
        13, 1, 'M, ') + ' S';

Answer 3

Finally found a great solution at this link, SQL - Seconds to Day, Hour, Minute, Second thanks for the help though folks, it got me further into this issue and searching for the right info.