August 9, 2010 at 8:56 am
We have a number column (Duration) that stores data in seconds. How can we return the data in hours (7200/60/60) then sum the hours? Any help will be greatly appreciated.
August 9, 2010 at 9:05 am
sum(myHoursCalculationHere)
looks like 7200/60/60 is one of the values you have? So in this case you would be looking for 1. Keep in mind that unless you do something like 7200/60/60.0 you will get values truncated to integers.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 9, 2010 at 9:13 am
DECLARE @TABLE AS TABLE(
[TIME] INT)
INSERT INTO @TABLE
SELECT 1
UNION ALL SELECT 60
UNION ALL SELECT 3600
UNION ALL SELECT 7200
SELECT totmin / 3600 AS [Total Hours]
FROM (SELECT CONVERT(DECIMAL(19, 5), SUM([TIME])) AS totmin
FROM @TABLE) TEMP
Bear in mind that this gives you the hours as 3.0169444444, where the 0.0169444444 is of an hour not the number of minutes/seconds.
August 9, 2010 at 4:38 pm
SELECT CAST(CAST(Duration AS decimal(11, 2)) / 3600 AS decimal(6, 2)) AS DurationInHrs
Scott Pletcher, SQL Server MVP 2008-2010
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply