Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2003
    Location
    Angola
    Posts
    12

    Unanswered: Adding Hours, Minutes, Seconds (SQL 2000)

    Hi There,

    I would like to find the sum of a column with a date format of '01:10:10' which is the hours:minutes:seconds from multiple rows.

    For instance, "01:50:10" + "01:20:5" = "3:10:15"

    Any ideas?

    Using SQL 2000

  2. #2
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    try this tricky thing....

    Code:
    declare @Dt as datetime
    set @Dt = '2007-02-20'
    declare @Dt1 as datetime
    set @Dt1 = '2007-02-20 01:50:10'
    declare @Dt2 as datetime
    set @Dt2 = '2007-02-20 01:20:05'
    
    select convert(varchar,cast((cast(@Dt1 as float) - cast(@Dt as float)) + (cast(@Dt2 as float) - cast(@Dt as float)) as datetime),114)
    now dont ask me what will happen if the sum is more than 24 hrs etc. etc...

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Code:
    select	sum(datediff(s, '2000-01-01', '2000-01-01 ' + [TimeString]))
    from	[YourTable]
    You'll need to verify that the above function syntax is correct, but you should get the general idea.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    declare @tm1 datetime, @tm2 datetime
    select @tm1='23:50:10', @tm2='23:20:05'
    select 'sum1'=
    str((datediff(s,0,@tm1)+datediff(s,0,@tm2))/60/60,4,0)
    +right(convert(char(8),dateadd(s,datediff(s,0,@tm2 ),@tm1),108),6)

    sum1
    ----------
    47:10:15


    Thanks upalsen, I didn't know it was that ease to convert between gregorian date and julian day number.
    select 'JulianDayNo'=convert(float,getdate())+2415020.5

    JulianDayNo
    ---------------------
    2454154.9927028548
    Last edited by pdreyer; 02-26-07 at 02:57.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I really don't think the formula needs to be that complicated...
    Code:
    set nocount on
    declare	@TimeStrings table (TimeString varchar(8))
    
    insert into @TimeStrings (TimeString) values ('01:50:10')
    insert into @TimeStrings (TimeString) values ('01:20:5') 
    
    select	sum(datediff(s, '2000-01-01', '2000-01-01 ' + TimeString)) as TotalSeconds,
    	convert(varchar(8), dateadd(s, sum(datediff(s, '2000-01-01', '2000-01-01 ' + TimeString)), 0), 8) as DateString
    from	@TimeStrings
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Apr 2003
    Location
    Angola
    Posts
    12
    UPalsen's way works - thanx

Posting Permissions

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