Add Hours, min and sec - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Add Hours, min and sec

Author

Topic

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-04-28 : 14:08:36

Here is the sql from my clocking_details.


Select emp_id, convert(varchar,clocking_date,101)Clocking_Date,time_in,time_out,hours,
cast(datediff(ss,time_in,time_out)/3600 as varchar)+':'+cast((datediff(ss,time_in,time_out)%3600)/60 as varchar)+':'+ cast(datediff(ss,time_in,time_out)%60 as varchar) as total
from dbo.CLOCKING_DETAILS
where (emp_id=76) and (clocking_date between '8/1/2008' and '8/16/2008')

this gives hours

I need one row for each clocking_date. so for the first record it should look like 76, 8/1/2008, 8:07:07 (8 hours, 7 min, 7 sec).

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-04-28 : 14:43:11

I tried this and it's adding but not in the fashion I wanted the result to be.
select distinct max(convert(varchar,clocking_date,101)),
sum((DateDiff(second, time_in, time_out)) / 3600) as Hours,
sum(((DateDiff(second, time_in, time_out)) % 3600) / 60) as Minutes,
sum((DateDiff(second, time_in, time_out)) % 60) as Seconds
from dbo.CLOCKING_DETAILS
where (emp_id=76) and (clocking_date between '8/1/2008' and '8/16/2008')
group by clocking_date

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-04-28 : 14:55:01

Looks like this one is doing what I need

select max(emp_id) eid, max(convert(varchar,clocking_date,101)) tDate,
sum(TotalSeconds) / 3600 as Hours,
(sum(TotalSeconds) % 3600) / 60 as Minutes,
sum(TotalSeconds) % 60 as Seconds
from
(select emp_id, DateDiff(second, time_in, time_out) as TotalSeconds, clocking_date
from dbo.CLOCKING_DETAILS
where (emp_id=76) and (clocking_date between '8/1/2008' and '8/15/2008'))x
group by clocking_date

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources