Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2006
    Posts
    3

    Question Unanswered: 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.

  2. #2
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up

    Code:
    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(second,'19000101','1900-01-01T'+timespend+':00')),'19000101'),8) 
    as t_timespend
    from #timetable
    group by username
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  3. #3
    Join Date
    Jan 2006
    Posts
    3
    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:-

    Code:
    select empid,
    convert(varchar(5),dateadd(second,sum(datediff(second,'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.

  4. #4
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up

    try this select query on ur table (I hope data is in 'hh:mm' format)
    Code:
     
    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(datediff(second,'19000101','1900-01-01T'+ltrim(rtrim(timespend))+':00')),'19000101'))
    as days,
    convert(varchar(5),dateadd(second,sum(datediff(second,'19000101','1900-01-01T'+ltrim(rtrim(timespend))+':00')),'19000101'),8) as times
    from #timetable
    group by username
    ) as tm
    Last edited by mallier; 01-05-06 at 11:10.
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  5. #5
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    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
    Cheers....

    baburajv

  6. #6
    Join Date
    Jun 2003
    Posts
    269

    Cool

    Quote Originally Posted by baburajv
    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
    that wont help him,b'cos datatype of time column is char and he dont want see the date value too.
    Last edited by mallier; 01-05-06 at 10:42.
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •