T-SQL duration in hours:minutes:seconds

I have average duration between several dates (DATETIME format) ie. 1900-01-01 01:30.00.00. How can I convert DATETIME to format hours:minutes:seconds where hours can be more that 24 - output format can be VARCHAR.

IE.

1 days 12 hours 5 minutes convert to 36:05:00
2 days 1 hour 10 minutes 5 seconds convert to 49:10:05

etc...

DECLARE @date1 DATETIME = '2011-08-03 13:30'
DECLARE @date2 DATETIME = '2011-08-03 13:00'
DECLARE @date3 DATETIME = '2011-08-03 14:00'
DECLARE @abc DATETIME = '2011-08-03 12:00'

select CAST(AVG(CAST(data-@abc as float)) as datetime)
from
(
    select 'data' as lab开发者_高级运维el, @date1 as data
union all
select 'data' as label, @date2 as data
union all
select 'data' as label, @date3 as data
) as a
group by label

I would like to get result as 01:30:00 which means that average time is 1 hours and 30 minutes.

I tried it:

CONVERT(VARCHAR(10), CAST(AVG(CAST(data-@abc as float)) as datetime), 108)

but then I get only time portion in HH:MM:SS. When I set @abc = 2011-08-02 then the results will be the same - this is incorrect.

King regards, Marcin

In T-SQL a datetime is precisely that, a date and a time where the hours can never exceed 24 because that moves it to the next day. You could use datepart to piece the datetime values out and treat them as integers and then rejoin them into the string you want. Depending on your final goal, you may be better of doing this type of work in your application or presentation layers where more general purpose languages often have more robust datetime libraries to work with.

I think you need to write a scalar-valued function that takes an integer argument (time difference in seconds) and format it as needed. For example,

 CREATE FUNCTION intToDateTime ( @time_in_secs BIGINT) RETURNS VARCHAR(30)
 AS 
 BEGIN
   DECLARE @retval VARCHAR(30);
   SET @retval = cast(@time_in_secs/(60*60) as varchar(10))+':'+
   cast( (@time_in_secs-@time_in_secs/(60*60)*3600)/60 as varchar(10))+':'+
   cast( (@time_in_secs-@time_in_secs/(60)*60) as varchar(10));
 return @retval;
END

This function needs some changes - you may want to display leading zero for 0-9(i.g. '00' instead of '0' as this function currently does); also you need to handle negative values in a better way.
Now you can use it with DATEDIFF(second, @val1,@val2).
Hope I pointed you to the right direction.

select cast(cast(cast(t as float) *24 as int) as varchar) + right(convert(varchar,t, 20), 6)
from(
select cast(AVG(CAST(data-@abc as float)) as datetime) t
from 
( 
    select 'data' as label, @date1 as data 
union all 
select 'data' as label, @date2 as data 
union all 
select 'data' as label, @date3 as data 
) as a 
group by label 
) a

Result:

1:30:00

You can't convert datetime to handle non-real dates and times.

However, you can get an output that looks like a datetime, simply by concatenating an hours string with ':' with minutes, etc.

Lookup the DATEADD() and DATEDIFF() functions...