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
Regards
Rajesh.
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=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />
Sorry, misunderstood your question. Try this:
CREATE TABLE #temp_table
(
hm VARCHAR(5)
)
INSERT #temp_table VALUES(‘2.30’)
INSERT #temp_table VALUES(‘5.55’)
SELECT
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
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
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 —————————————-
http://spaces.msn.com/members/dineshasanka
]]>