locked
Sum a DATETIME field RRS feed

  • Question

  • Just noticed it isn't straight forward to SUM a DATETIME fields.

    I’ve used the following code to get the time, how do I convert the output back to a DATETIME field?

    declare @T table
    (
      total_hours_worked datetime
    )
    insert into @T values ('01:00:00')
    insert into @T values ('01:30:00')
    insert into @T values ('03:35:00')
    select * 
    from @T
    select SUM((DATEPART(hh,total_hours_worked)*60)+DATEPART(mi,total_hours_worked)+(DATEPART(ss,total_hours_worked)/(60.0)))/60.0  as TotalHours from @T

    Thanks,

    • Changed type Kalman Toth Saturday, August 16, 2014 2:39 PM
    Thursday, July 31, 2014 9:28 AM

Answers

  • The example below returns a datetime of the duration worked with a base date of 1900-01-01. 

    SELECT
    	DATEADD(second, SUM(DATEDIFF(second, '', total_hours_worked)), '') AS TotalHours
    FROM @T;

    If you are using SQL 2008 or above, you can cast the value as time(0) to ignore the date part.  Below is a method to return whole days as an integer and the remaining hours as a time:

    SELECT
    	 DATEDIFF(day, '', DATEADD(second, SUM(DATEDIFF(second, '', total_hours_worked)), '')) AS TotalDays
    	,CAST(DATEADD(second, SUM(DATEDIFF(second, '', total_hours_worked)), '') AS time(0)) AS TotalHours
    FROM @T;


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Proposed as answer by Kalman Toth Saturday, August 16, 2014 2:40 PM
    • Marked as answer by pituachMVP Thursday, September 25, 2014 3:10 PM
    Thursday, July 31, 2014 10:14 AM
  • Yes, working with datetime is a little awkward in Transact SQL.  I'm sure you noticed when you query your table all of the strings were implicitly converted to datetimes, with the date portion being Jan 1 1900 (SQL Server's "Day 0").   

    I just started a discussion thread in this forum regarding implicit conversions that may be of interest  (Implicit Conversions).

    The earlier post is correct.  Summing dates just doesn't make sense.  What you are trying to do is sum time spans.  I would go in the direction of storing these times spans as floating point (real/float) -- you could use fixed precision numbers (e.g. decimal as well)  numbers.   Of course once you've represented them as numbers, summing becomes a trivial matter.

    Here is the "NewTimeSpan" function I referenced in the aforementioned discussion thread.  It wouldn't take much work to come up with a version that accepts a string formatted as your example above.

    CREATE FUNCTION dbo.NewTimeSpan 
    (
    	 @pDays int
    	,@pHours int = 0
    	,@pMinutes int = 0
    	,@pSeconds decimal(5,3) = 0
    )
    RETURNS float(53)
    AS
    BEGIN
    	DECLARE @Result float(53)
    	SELECT @Result = @pDays+(((@pHours/24.0)+(@pMinutes/(24.0*60)))+(@pSeconds/(24.0*60*60)))
    	RETURN @Result
    END
    GO

    Once the time spans are summed, you may want to create a scalar function that convert's the result into a pretty formatted string.  


    • Edited by George Robertson Jr Saturday, August 16, 2014 10:44 AM
    • Proposed as answer by Kalman Toth Saturday, August 16, 2014 2:40 PM
    • Marked as answer by pituachMVP Thursday, September 25, 2014 3:10 PM
    Saturday, August 16, 2014 10:42 AM

All replies

  • Hi,

    What is your expected end result? Are you trying to add up your value with any date?

    Regards,

    Brindha.

    Thursday, July 31, 2014 9:40 AM
  • Hi,

    What is your expected end result? Are you trying to add up your value with any date?

    Regards,

    Brindha.


    the end result should be 06:05:00
    Thursday, July 31, 2014 9:54 AM
  • The example below returns a datetime of the duration worked with a base date of 1900-01-01. 

    SELECT
    	DATEADD(second, SUM(DATEDIFF(second, '', total_hours_worked)), '') AS TotalHours
    FROM @T;

    If you are using SQL 2008 or above, you can cast the value as time(0) to ignore the date part.  Below is a method to return whole days as an integer and the remaining hours as a time:

    SELECT
    	 DATEDIFF(day, '', DATEADD(second, SUM(DATEDIFF(second, '', total_hours_worked)), '')) AS TotalDays
    	,CAST(DATEADD(second, SUM(DATEDIFF(second, '', total_hours_worked)), '') AS time(0)) AS TotalHours
    FROM @T;


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Proposed as answer by Kalman Toth Saturday, August 16, 2014 2:40 PM
    • Marked as answer by pituachMVP Thursday, September 25, 2014 3:10 PM
    Thursday, July 31, 2014 10:14 AM
  • That's funny - reminds me of when I started out in AMP many years back. Old version of SQL. There are better ways of course. Dans about covered it. Depending on your SQL version you could opt for convert(char, ....., 14) instead of cast.
    Thursday, July 31, 2014 10:48 AM
  • Hi,

    Try this..

    SELECT CONVERT(VARCHAR,DATEADD(ms,(SUM((DATEPART(hh,total_hours_worked)*3600)+DATEPART(mi,total_hours_worked)*60+(DATEPART(ss,total_hours_worked)/(60.0))))*1000,0),108)  as TotalHours FROM @t

    Regards,

    Brindha.

    Thursday, July 31, 2014 11:37 AM
  •  Try string functions..

    Thursday, July 31, 2014 12:02 PM
  • Just for entertainment's sake I should point out you can achieve the same result via simple casts ie

    cast(sum(cast(total_hours_worked as real)) as datetime)

    Thursday, July 31, 2014 12:14 PM
  • And what happens when the total number of hours >= 24 hrs?  Since you don't really have a actual starting datetime, what value do you expect when you convert this "sum" back into a datetime?  What happens when the date portions of your column are not all the same?  Without some important simplifying assumptions, this approach is doomed.  You can search the forums on "timespan" to find similar discussions .
    Thursday, July 31, 2014 12:47 PM
  • The problem/confusion comes about because people mix up the TIME datatype with a format of representing time in hours, minutes and seconds.  25:34:17 is a valid way of representing something that lasts 25 hours, 34 minutes and 17 secs - same as 1534 minutes and 17 secs, or even 92,057 seconds.  But it is not a valid TIME.

    BOL is clear that the TIME datatype is :

    Defines a time of a day. The time is without time zone awareness and is based on a 24-hour clock.


    Thursday, July 31, 2014 1:03 PM
  • cast(sum(cast(total_hours_worked as real)) as datetime)

    When using this approach the days part is indeed also summed. The above approach is about as simple as it gets. With regards to displaying days, if this is indeed within scope and required (it's not indicated it is), you could simply do a datediff(day, '',cast(sum(cast(total_hours_worked as real)) as datetime)) to spit that bit out first, then follow it up with your time formatted however you use it. I was a bit mystified by storing just a time in a datetime field. Crossed my mind it might be an old version, but either way, works. Perhaps datetime is being used there to cater for DAYS too.


    Monday, August 4, 2014 1:24 PM
  • Yes, working with datetime is a little awkward in Transact SQL.  I'm sure you noticed when you query your table all of the strings were implicitly converted to datetimes, with the date portion being Jan 1 1900 (SQL Server's "Day 0").   

    I just started a discussion thread in this forum regarding implicit conversions that may be of interest  (Implicit Conversions).

    The earlier post is correct.  Summing dates just doesn't make sense.  What you are trying to do is sum time spans.  I would go in the direction of storing these times spans as floating point (real/float) -- you could use fixed precision numbers (e.g. decimal as well)  numbers.   Of course once you've represented them as numbers, summing becomes a trivial matter.

    Here is the "NewTimeSpan" function I referenced in the aforementioned discussion thread.  It wouldn't take much work to come up with a version that accepts a string formatted as your example above.

    CREATE FUNCTION dbo.NewTimeSpan 
    (
    	 @pDays int
    	,@pHours int = 0
    	,@pMinutes int = 0
    	,@pSeconds decimal(5,3) = 0
    )
    RETURNS float(53)
    AS
    BEGIN
    	DECLARE @Result float(53)
    	SELECT @Result = @pDays+(((@pHours/24.0)+(@pMinutes/(24.0*60)))+(@pSeconds/(24.0*60*60)))
    	RETURN @Result
    END
    GO

    Once the time spans are summed, you may want to create a scalar function that convert's the result into a pretty formatted string.  


    • Edited by George Robertson Jr Saturday, August 16, 2014 10:44 AM
    • Proposed as answer by Kalman Toth Saturday, August 16, 2014 2:40 PM
    • Marked as answer by pituachMVP Thursday, September 25, 2014 3:10 PM
    Saturday, August 16, 2014 10:42 AM
  • I should have added the disclaimer that my solution would require total_hours_worked to be stored as either a float or a varchar to be used with the function I described above.   The completed functions would be similar to 

    CREATE FUNCTION dbo.TimeSpan_CreateFromString 
    (
    	 @pFormattedTimeSpan varchar(128) = null
    )
    RETURNS float(53)
    with returns null on null input AS
    BEGIN
       declare @DayPortion int
              ,@HourPortion int
              ,@MinutePortion int
              ,@SecondPortion decimal(6,4)
              
       declare @l int
       declare @s2 varchar(128) 
    
    	DECLARE @Result float(53)
    
       select @s2=LTRIM(rtrim(@pFormattedTimeSpan))
       set @l = LEN(@s2)
       declare @i int 
       if CHARINDEX('.',@s2,1) < CHARINDEX(':',@s2,1)
       begin
       set @i = CHARINDEX('.',@s2,1)
       select @DayPortion = 
          case CHARINDEX('.',@s2)
             when 0 then 0 
             else 
                case ISNUMERIC(SUBSTRING(@s2,1,CHARINDEX('.',@s2)-1))
                   when 1 then CAST(SUBSTRING(@s2,1,CHARINDEX('.',@s2)-1) as int) 
                   else 0 
             end
          end;
          set @s2=ltrim(rtrim(SUBSTRING(@s2,@i+1,@l-(@i))))
       end
       else
       begin
          set @DayPortion=0
       end
       -- ** debug: select @i, @l, @s2
    
       --select CHARINDEX(':',@s2,1) [c1], CHARINDEX(':',@s2,CHARINDEX(':',@s2,1)+1) [c2], CHARINDEX(':',@s2,(CHARINDEX(':',@s2,CHARINDEX(':',@s2,1)+1))+1) [c3]
       set @i = CHARINDEX(':',@s2,1)
       select @HourPortion = 
          case CHARINDEX(':',@s2,1)
             when 0 then 0
             else
                case ISNUMERIC(SUBSTRING(@s2,1,CHARINDEX(':',@s2,1)-1)) 
                   when 1 then CAST(SUBSTRING(@s2,1,CHARINDEX(':',@s2,1)-1) AS int)
                   else null
                end
          end
          
       set @s2=ltrim(rtrim(SUBSTRING(@s2,@i+1,@l-(@i))))
       -- ** debug: select @i, @l, @s2
    
       set @i = CHARINDEX(':',@s2,1)
       select @MinutePortion = 
          case CHARINDEX(':',@s2,1)
             when 0 then 0
             else
                case ISNUMERIC(SUBSTRING(@s2,1,CHARINDEX(':',@s2,1)-1)) 
                   when 1 then CAST(SUBSTRING(@s2,1,CHARINDEX(':',@s2,1)-1) AS int)
                   else null
                end
          end
          
       set @s2=ltrim(rtrim(SUBSTRING(@s2,@i+1,@l-(@i))))
       -- ** debug: select @i, @l, @s2
    
       set @i = CHARINDEX(':',@s2,1)
       select @SecondPortion = 
          case ISNUMERIC(@s2)
             when 0 then null
             else CAST(@s2 as decimal(6,4))
          end
       	
    	SELECT @Result = dbo.TimeSpan_CreateFromParts(@DayPortion,@HourPortion,@MinutePortion,@SecondPortion);
    	RETURN @Result
    END
    GO
    

    I changed the function names to make them more consistent.

    Sunday, August 17, 2014 11:33 AM
  • It's a lot easier if you work with TIME fields...

    SELECT CAST(dateadd(MS,SUM(DATEDIFF(MS,0,TIMEFIELD)),0) AS TIME)

    Sunday, August 17, 2014 12:08 PM
  • It's a lot easier if you work with TIME fields...

    SELECT CAST(dateadd(MS,SUM(DATEDIFF(MS,0,TIMEFIELD)),0) AS TIME)

    It works fine until you exceed 24 hours.  

    declare @T table
    (
      total_hours_worked datetime
    )
    insert into @T values ('01:00:00')
    insert into @T values ('01:30:00')
    insert into @T values ('03:35:00')
    insert into @T values ('18:35:00')
    select * 
    from @T
    
    SELECT CAST(dateadd(MS,SUM(DATEDIFF(MS,0,total_hours_worked)),0) AS TIME)
    FROM @T;

    Sunday, August 17, 2014 11:22 PM
  • Thanks great Simon Hughes,

     its working and simple solution in single query

    Tuesday, January 14, 2020 10:48 AM