DateTime Problem | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

DateTime Problem

Hi All, i had a column named like "duration"
in this the values will be as 2.30,5.55 in the format of hh:mm How to do summation that correctly return the sum in HH:MM

You know, that this is not an optimal solution you have there?<br /><pre id="code"><font face="courier" size="2" id="code"><br />DECLARE @f DECIMAL(4,2)<br />SET @f = 5.55<br />SELECT <br /> CONVERT(CHAR(5),<br /> DATEADD(MINUTE, CAST(@f AS INT)*60, DATEADD(MINUTE, (@f – CAST(@f AS INT))*100, 0))<br /> , 10<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /><br /></font id="code"></pre id="code"><br /><br />–<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=></a><br />Heute schon gebloggt?<a target="_blank" href=></a><br />
Sorry, misunderstood your question. Try this:
CREATE TABLE #temp_table
INSERT #temp_table VALUES(‘2.30’)
INSERT #temp_table VALUES(‘5.55’)
CONVERT(VARCHAR, SUM(DATEDIFF(ss, ‘1900-01-01 00:00′, CONVERT(DATETIME, REPLACE(hm,’.’, ‘:’))))/3600) +
‘:’ +
CONVERT(VARCHAR, (SUM(DATEDIFF(ss, ‘1900-01-01 00:00’, CONVERT(DATETIME, REPLACE(hm, ‘.’, ‘:’))))%3600)/60)
FROM #temp_table DROP TABLE #temp_table ————————————————————-
8:25 (1 row(s) affected)

Frank Kalis
Microsoft SQL Server MVP
Heute schon gebloggt?

how about this select cast(sum(FLOOR (d)) +
floor(sum(d-FLOOR (d)) * 10 / 6) as varchar) +’:’+
cast(round(sum(d-FLOOR (d))* 100 – floor(sum(d-FLOOR (d)) * 10 / 6 ),2) as varchar) from dur d is a filed in table dur —————————————-