Computing SUM on DATETIME datatype

hi everybody,
i'm trying to calculate the 'SUM' of time spent in hrs. n min. How can i do this using SQL Server?
What i mean is, i've a column 'TIME_SPENT' that has 'datetime' datatype. This column saves time spent for an activity in format 'hh:mm'. Suppose a user spends 45min for activity 'A' and say 1hr 25 min for activity 'B' then i want to calculate the 'SUM' of 'TIME_SPENT' for the user which should appear as 'Total time spent =2:10'

Can somebody pls help me with this?

Thnx in advance.create table #timetable (username varchar(50),timespend varchar(8))

insert into #timetable values ('joe','03:01')
insert into #timetable values ('joe','00:01')
insert into #timetable values ('foo','00:03')
insert into #timetable values ('foo','01:02')



select username,
convert(varchar(5),dateadd(second,sum(datediff(sec ond,'19000101','1900-01-01T'+timespend+':00')),'19000101'),8)
as t_timespend
from #timetable
group by username|||hey thnx for ur reply mallier,

ur code works great for the example u explained. But when i try to run it for my table in the database it gives following error:-
'The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.'

My code goes as follows:-

select empid,
convert(varchar(5),dateadd(second,sum(datediff(sec ond,'19000101','1900-01-01T'+time_spent+':00')),'19000101'),8) as 'Time spent'
from timesheet
where empid=9
group by empid
can u pls guide me on this?

thnx once again.|||try this select query on ur table (I hope data is in 'hh:mm' format)


create table #timetable (username varchar(50),timespend char(8))

insert into #timetable values ('joe','03:01')
insert into #timetable values ('joe','00:01')
insert into #timetable values ('foo','00:03')
insert into #timetable values ('foo','01:02')



select username,case when days>0 then cast(days*24+cast(left(times,2) as int)as varchar)+':'+right(times,2)
else
times
end as total_time
from
(
select username,
datediff(day,'19000101',dateadd(second,sum(datedif f(second,'19000101','1900-01-01T'+ltrim(rtrim(timespend))+':00')),'19000101'))
as days,
convert(varchar(5),dateadd(second,sum(datediff(sec ond,'19000101','1900-01-01T'+ltrim(rtrim(timespend))+':00')),'19000101'),8 ) as times
from #timetable
group by username
) as tm|||create table #babu ( names varchar(10),times varchar(10))

insert into #babu values ('babu0','01:20')
insert into #babu values ('babu1','01:20')
insert into #babu values ('babu1','01:20')
insert into #babu values ('babu0','01:20')
insert into #babu values ('babu0','01:20')

select names, dateadd(second,sum(datediff(second,'1900-01-01',convert(datetime,times))),'1900-01-01') from #babu group by names|||create table #babu ( names varchar(10),times varchar(10))

insert into #babu values ('babu0','01:20')
insert into #babu values ('babu1','01:20')
insert into #babu values ('babu1','01:20')
insert into #babu values ('babu0','01:20')
insert into #babu values ('babu0','01:20')


select names, dateadd(second,sum(datediff(second,'1900-01-01',convert(datetime,times))),'1900-01-01') from #babu group by namesthat wont help him,b'cos datatype of time column is char and he dont want see the date value too.

Complex query

Hi everybody,

I have a table storing the quiz data of the students like this:

Date | Time | Name | Grade

I can show the data of specific student by the query below:

SELECT *
FROM tbl_quiz
WHERE name ='xxx'

Suppose I have 10 records for each student, and I wanna display the
data like this:

Previous quiz date | Previous quiz time | Date | Time | Name | Grade

How can I make the "Previous quiz date" and "Previous quiz time"? How
can I query it? Please advice

FRANK?? (lokalun@.gmail.com) writes:
> I have a table storing the quiz data of the students like this:
> Date | Time | Name | Grade
> I can show the data of specific student by the query below:
> SELECT *
> FROM tbl_quiz
> WHERE name ='xxx'
> Suppose I have 10 records for each student, and I wanna display the
> data like this:
> Previous quiz date | Previous quiz time | Date | Time | Name | Grade
> How can I make the "Previous quiz date" and "Previous quiz time"? How
> can I query it? Please advice

Is that date as 2006-04-02 and time as 14:30:21, or does any of them
include the other? Normally you store datetime values in one column in
SQL Server as there are no pure date and time data types. To make it
simpler, I'm assuming that time includes both date and time.

SELECT predate = c.date, prevtime = c.time,
a.date, a.time, a.name, a.grade
FROM tbl_quiz a
JOIN (SELECT time = MAX(time), name
FROM tbl_quiz
GROUP BY name) AS b ON a.name = b.name
JOIM tbl_quiz c ON c.name = b.name
AND c.time = (SELECT MAX(d.time)
FROM tbl_quiz d
WHERE d.name = b.name
AND d.time < b.time)
WHERE a.name = 'xxx'

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Sommarskog, thanks for your posting. I may not clearly describe my
situation. I want to show the previous quiz result with the current
quiz detail. If I show the 1st quiz result, there will be no previous
quiz date and time; If I show the 2nd quiz result, I can show 1st quiz
date and 1st quiz time as well, and so on. Is your solution still works
in this situation?|||It will be much better if u can give me some idea if I would like to do
this:

Previous quiz date | Previous quiz grade | Date | Time | Name | Grade |
Next quiz date | Next quiz grade

That is, if I show the 4th quiz detail, it will display the 3rd quiz
date and grade and 5th quiz date and grade as well. Is that impossible?

Please let me know if u want more information. Many thanks.

FRANK|||?? (lokalun@.gmail.com) writes:
> It will be much better if u can give me some idea if I would like to do
> this:
> Previous quiz date | Previous quiz grade | Date | Time | Name | Grade |
> Next quiz date | Next quiz grade
> That is, if I show the 4th quiz detail, it will display the 3rd quiz
> date and grade and 5th quiz date and grade as well. Is that impossible?
> Please let me know if u want more information. Many thanks.

It is at this point that I will ask to provide:

o CREATE TABLE statements for your table.
o INSERT statemetns with sample data.
o The desired data given the sample.

This makes it easy to copy-and-paste test data into a query tool, and
develop a tested solution.

Moreover, if you don't give us a clear specification of what you want,
you cannot not get what you want.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

You also do not know that rows are not records, what reserved words are
or how to name data elements. In T-SQL, there is s DATETIME data type
which is like the Standard SQL's TIMESTAMP.

CREATE TABLE QuizScores
(student_name CHAR(20) NOT NULL,
quiz_date DATETIME NOT NULL,
quiz_grade INTEGER NOT NULL,
PRIMARY KEY (student_name, quiz_date));

We already posted several verisions of this problem. Look for a posting
about the 2nd edition of my Puzzles book.