December 17, 2002 at 2:34 am
Hi All,
Please help me.
I have a column which is saving TIME in hh:mm:ss data type is datetime.
I need get total hour from this column, have any function or formula to use calculate time value ?
Example :
Empl_Date Empl_code Estimate_Hours
---------- --------- -------------------------
2002-10-20 JAA0001 1900-01-01 03:30:00.000
2002-11-20 JAA0001 1900-01-01 01:30:00.000
2002-12-20 JAA0001 1900-01-01 02:15:00.000
-------Total Hours : 1900-01-01 06:15:00.000 (how to sum(Hours) ?)
Thx,
Jonny
December 17, 2002 at 3:13 am
Do you want to just sum the hours or the whole time value?
To just sum the hours: -
select sum(datepart(hh,Estimate_Hours)) from Table
I think you want to sum the whole time value (07:15:00.000 in your example??). This is more difficult, I'm not sure it can be done without a complex algorithm.
Regards,
Andy Jones
.
December 17, 2002 at 3:44 am
I think this will do it, not sure if there is a simpler way: -
select
convert(varchar(2),sum (datepart(hh,Estimate_Hours)) + (sum(datepart(mi,Estimate_Hours)) + sum(datepart(ss,Estimate_Hours)) / 60) / 60)
+':'
+convert(varchar(2),(sum(datepart(mi,Estimate_Hours)) + sum(datepart(ss,Estimate_Hours)) / 60) % 60)
+':'
+convert(varchar(2),sum(datepart(ss,Estimate_Hours)) % 60)
from Table2
(Returns a string)
Regards,
Andy Jones
.
December 17, 2002 at 4:39 am
Also try these 2 (sorry I rolled into one but gives 2 output)
First output column is houtrs expressed as total hours and hundredths of an hour and will span days. Your example will out 7.25.
The second output is expressed as a string in the format hhh:mm:ss. You example will output 7:15:00. This also will span more than one day so 30 hours 15 minutes will output 30:15:00.
select
CAST(((datediff(day,'1/1/1900',dateadd(s,sum(datediff(s, '1/1/1900',Estimate_Hours)), '1/1/1900')) * 24) + datepart(hh,dateadd(s,sum(datediff(s, '1/1/1900',Estimate_Hours)), '1/1/1900')) + (datepart(minute,dateadd(s,sum(datediff(s, '1/1/1900',Estimate_Hours)), '1/1/1900')) / 60.0)) as numeric(10,2)) as totalhoursdecimal,
CAST((datediff(day,'1/1/1900',dateadd(s,sum(datediff(s, '1/1/1900',Estimate_Hours)), '1/1/1900')) * 24) + datepart(hh,dateadd(s,sum(datediff(s, '1/1/1900',Estimate_Hours)), '1/1/1900')) as VARCHAR(5)) + ':' + CAST(CAST(datepart(minute,dateadd(s,sum(datediff(s, '1/1/1900',Estimate_Hours)), '1/1/1900')) as VARCHAR(2)) + '0' as char(2)) + ':' + CAST(CAST(datepart(s,dateadd(s,sum(datediff(s, '1/1/1900',Estimate_Hours)), '1/1/1900')) as VARCHAR(2)) + '0' as char(2)) as totalhhmmssinstring
from Table2
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply