February 16, 2009 at 10:35 am
Hello,
Was wondering - if someone has any suggestions - whereby which we can add time durations.
Example: The Table looks like this:
Date No. of Hours Worked Type
01/02/2009 10.29 1
01/03/2009 2.67 2
and so on ...
Now - if we were to query the total no. of hours worked for Type 1 for the Month of January 2009 - any suggestions !!!
Their is a way about going about it - by converting it into varchar, then splitting the : and taking the summation of the hours and the sumation of the minutes - which is to be converted again into hours and minute ...
Any simpler suggestion pls !!! ...
February 16, 2009 at 12:46 pm
Why can't you just use SUM? The data you show looks like it is hours and decimal part of the hour (2.67), not seconds so a SUM should work. Do you want to show hours and minutes instead of hours and decimal part of the hour (2.67)?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 16, 2009 at 12:50 pm
[font="Verdana"]You can try casting to datetime, then using datediff() to calculate the number of seconds.[/font]
February 16, 2009 at 1:34 pm
Either format the total hours worked in front end, or if this is not applicable something like:
SELECT CONVERT(varchar(10), CONVERT(int, SUM(HoursWorked))) + ':'
+ RIGHT('0' + CONVERT(varchar(2), CONVERT(int, FLOOR(60.0 * SUM(HoursWorked) + 0.5)) % 60), 2)
FROM ...
If the sum of the hours worked was guaranteed to be less than 24 hours then you could use the following, but I don't think this will work for you in this case.
SELECT CONVERT(varchar(5), DATEADD(minute, CONVERT(int, FLOOR(60.0 * SUM(HoursWorked) + 0.5)), 0), 108)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply