How to Sum up Datetime - 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

General SQL Server Forums

New to SQL Server Programming

How to Sum up Datetime

Author

Topic

tonna
Starting Member

4 Posts

Posted - 2010-09-27 : 05:39:19

Dear Experts,

I Just Begining with SSRS Sql server 2005 and i was stuck with this problem for 4 days and i really want to know how to sum up Datetime ,because i cannot use aggregation funcion Sum() to sum up Datetime

Example
I had this data for 1 user Login
DatetimeLogin 1/10/2010 3:00:00 PM , DatetimeLogout 9/10/2010 3:00:00 PM
and i want to know Amount of time so i use Expression in SSRS
= Field!.DatetimeLogout.Value - Field!.DatetimeLogin.Value
is give my result is 243.00:00:00
Those information is about 1 person ,and i really want to know is i want to sum up the result time( same as my result format 243.00:00:00 (Day.HH.mm.ss) ) for each person and in SSRS Expression cannot use
= Sum(Field!.AmountTime.Value)
,So anybody who know how to resolve this problem, Could You please Suggestion for me? That's very kind of You.

Thank you Very Much. I Looking forward for your answer....

tonliuyifei

Sachin.Nand

2937 Posts

Posted - 2010-09-27 : 06:10:05

[code]
declare @dt1 datetime='1/10/2010 3:00:00 PM'
declare @dt2 datetime=' 1/11/2010 10:27:53 PM'

select DATEDIFF(HOUR,@dt1,@dt2)Hrs,DATEDIFF(MINUTE,@dt1,@dt2)%60 Minutes,DATEDIFF(SECOND,@dt1,@dt2)%60 Seconds
[/code]

PBUH

tonna
Starting Member

4 Posts

Posted - 2010-09-27 : 11:35:13

Thank you for your answer sir, But still not clear about how can i sum up
Datetime sir , Could you please give me an example?
if i want to sum up date 1/10/2010 3:00:00 PM + 9/10/2010 3:00:00 PM
Thank you again ,That's very kind of you. I looking forward for your answer...

tonliuyifei

Sachin.Nand

2937 Posts

Posted - 2010-09-28 : 01:31:33

I fail to understand what do you mean by "sum of dates".
What should be the o/p of "1/10/2010 3:00:00 PM + 9/10/2010 3:00:00 PM"?

PBUH

tonna
Starting Member

4 Posts

Posted - 2010-09-28 : 02:40:26

Dear Sir,

I'm sorry, I Just want to know if i have amount of time
for example

DatetimeLogout - DatetimeLogin = during time (this for 1 user)

,but i want to know if i want to sum up during time ,how should i do?
because statement aggregation SUM(during time) cannot use Could you please give me an example code C# to add in SSRS report please.

Thank you Sir, That's very kind of you , I looking forward for your answer...

tonliuyifei

Sachin.Nand

2937 Posts

Posted - 2010-09-28 : 03:11:33

The logic behind the code is convert the difference into minutes for each userid & then sum the minutes for each userid & then change the aggregated minutes to Hours,Minutes & Seconds.Little more stringent testing might be needed.
I cannot give you C# code because I am not so much of a C# person.But you can acheive it in SQL easily


declare @times as table  (userid int,starttime datetime, endtime datetime)
insert into @times values (1,'1/11/2010 3:00:00 PM', '1/11/2010 3:05:00 PM')
insert into @times values (1,'1/11/2010 3:00:00 PM', '1/12/2010 3:05:10 PM')
insert into @times values (2,'02/10/2010 06:15:00 PM', '02/10/2010 8:05:10 PM')
insert into @times values (2,'02/11/2010 04:15:00 PM', '02/11/2010 6:05:10 PM')

 select userid,
        sum(datediff(mi,starttime, endtime))/60 Hrs,
        sum(datediff(mi,starttime, endtime))%60 Minutes,
        sum(datediff(ss,starttime, endtime))%60 Seconds
from @times
group by userid

PBUH

tonna
Starting Member

4 Posts

Posted - 2010-09-29 : 05:56:43

Thank you very very much sir, your answer is very useful for me.
now i can resolve my problem ,i really happy ,Thank you again. That's very kind of you.

tonliuyifei

Sachin.Nand

2937 Posts

Posted - 2010-09-29 : 05:59:57

quote:
Originally posted by tonna

Thank you very very much sir, your answer is very useful for me.
now i can resolve my problem ,i really happy ,Thank you again. That's very kind of you.

tonliuyifei

You are most welcome.

Also it will be very kind of you if stop calling me "SIR" as I to have name.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources