Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Feb 2005
    Location
    Ferrara (Italy)
    Posts
    11

    Unanswered: Sum Time in sql server

    Ciao,
    I'm a beginner programmer and I am in trouble about the sum of a time field.
    This is may problem:
    I woud like the sum the time of datetime column and get tha amount

    Example:

    08:00 + 12:00 + 05:00 + 17:00 = 42 Hours

    Thank you very much

    Davide

  2. #2
    Join Date
    Jan 2005
    Location
    TempDb
    Posts
    228
    You can do this with DateDiff(), however, you need to ensure your dates are valid. If you are not storing relevant dates, you need to manipulate your values to ensure they have the correct date portion. That will require a sequential value as a reference.

    Post sample data if you need more help. I was unable to figure out how you came to 42 (which, coincidentally(?), is the answer to THE QUESTION - see Hitchhikers Guide to the Galaxy for THE QUESTION):
    Select DateDiff(hh, '01/01/2005 08:00', '01/01/2005 12:00')
    , DateDiff(hh, '01/01/2005 12:00', '01/02/2005 05:00')
    , DateDiff(hh, '01/02/2005 05:00', '01/02/2005 17:00')
    , DateDiff(hh, '01/01/2005 08:00', '01/02/2005 00:00')
    + DateDiff(hh, '01/02/2005 00:00', '01/02/2005 05:00')
    + DateDiff(hh, '01/02/2005 05:00', '01/02/2005 17:00')

  3. #3
    Join Date
    Feb 2005
    Location
    Ferrara (Italy)
    Posts
    11

    Red face

    Dear Maxa,
    thank you very much for your reply,
    but unfortunately explained in bad way my request, I need the amount of a whole datetime column.
    example:

    Select sum (time1) as Amount time (Doesn't work)
    from Times
    where startday = 01012005 and endday = 31012005

    The problem is that the SUM function doesn't work with datetime field
    I need to calculate the total of datetime column.

    I'm studying and I understood that I should dinamically convert the datetime value in float for each record, after make the SUM of this float field for each record and at the end to convert the float value in datetime.
    can you help me??

    Thank you / Grazie

    Davide

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what are the date values that you use for those times?

    you could try this -- extract the times, convert them to seconds, sum the seconds, then convert the seconds back to a time

    but this will be tricky because you must always have a date involved in the calculations

    can you not change the datatype of the column to something more reasonable, like an integer (e.g. to represent seconds)?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2005
    Location
    Ferrara (Italy)
    Posts
    11

    Wink Thank you Rudy

    Thank you Rudy for your help,
    I'm working about a program to calculate the amount of time work of personnel in my office.
    This is a real example of my table:

    PersonalID(Char4) | Surname(Varchar 40)| Name(Varchar 40) | TimeIn(Datetime) | TimeOut(DateTime) | TotalTime(DateTime)

    The Field TotalTime is calculated: TimeOut - TimeIn

    Now I need to Sum the column TotalTime because my chief Has to pay exactly the time spent to work.
    I cannot change the structure of the table because it's property of a program to read the badges.

    Thank you again

    Davide

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    can you do me a favour, run this query e to display a few values:
    Code:
    select top 10
           PersonalID
         , convert(char(20),TimeIn,120) as i
         , convert(char(20),TimeOut,120) as o
         , convert(char(20),TotalTime,120) as t
      from Times
    order
        by 3 desc
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2005
    Location
    Ferrara (Italy)
    Posts
    11

    Wink I'll try

    Dear Rudy,
    thank you again, tomorrow I'll try.
    Reading the newsgroup and studying on the manual I would like to try something like this:

    Select sum(datepart(hour,convert(datetime,TotalTime))) from AS TotalHours
    from MyTable -- Sum Hours

    Select sum(datepart(minute,convert(datetime,TotalTime))) AS TotalMinutes
    from MyTable -- Sum Minutes

    OR another solution could be:

    Select Sum(DateDiff("hh", TimeOut, TimeIn)) As TotalHours
    from MyTable -- Sum Hours

    Select Sum(DateDiff("mm", TimeOut, TimeIn)) As TotalMinutes
    from MyTable -- Sum Minutes

    Do you think that It could work?

    Thank you again

    Davide

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    did you try the query i suggested? i would like to see the results

    yes, your SUM of DATEDIFFs should work, except the earlier time has to go first

    in the DATEPARTs you use CONVERT, and that should not be necessary
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2005
    Location
    Ferrara (Italy)
    Posts
    11

    Unhappy I tested

    Ciao,
    I sorry for delay but for mr was impossible to go to work and test your code because I was sick, now is better I went to office to work.
    Here there is the code that you suggested me.

    select top 10
    PersonalID
    , convert(char(20),TimeIn,120) as i
    , convert(char(20),TimeOut,120) as o
    , convert(char(20),TotalTime,120) as t
    from Times
    order
    by 3 desc


    Well:
    , convert(char(20),TimeIn,120) as i
    , convert(char(20),TimeOut,120) as o
    returns the date and time corretly! Very good
    The problem is with
    , convert(char(20),TotalTime,120) as t
    it returns NULL!!!!!!
    Really NULL, because this column is a calculated field of delphi program.
    If I open the table with a normal query I can see exactly the values, but with your procedure I see only NULL.
    Infact using the formula of sum of datepart of my formula with the column TotalTime I have strange values, I tried with the columns TimeIn and TimeOut and the sum of the values is correct.
    The problem is with the column TotalTime, strange, very strange, I can see the values with queries, but I can't convert it.
    Another question, I would like to convert the type from datetime to smalldatetime and I encountered an error taht is not possible to convert the type. I can I convert the type?

    Thank you very much!!

    Ciao

    Davide

  10. #10
    Join Date
    Jan 2005
    Location
    TempDb
    Posts
    228
    Provide DDL and sample data. However, the solution would seem to be:
    select top 10 PersonalID
    , convert(char(20),TimeIn,120) as i
    , convert(char(20),TimeOut,120) as o
    , convert(char(20),DateDiff(hh, TimeIn, TimeOut)) as t
    from Times
    order
    by 3 desc
    TotalTime needs to be expressed in terms of milliseconds or seconds or minutes or hours or a combination or etc. to be meaningful.
    I love deadlines. I like the whooshing sound they make as they fly by. Douglas Adams

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    nice catch, maxA -- your query at least provides an intermediate answer

    davidus, make sure you understand maxA's advice:
    TotalTime needs to be expressed in terms of milliseconds or seconds or minutes or hours or a combination or etc. to be meaningful.
    if you have an external program that is calculating the difference on each row, then it should store that difference not as a datetime, but as an integer -- i recommend seconds

    then you can sum it easily

    that was the final objective, to add up a bunch of times, right?

    if you have external program logic that calculates the difference already, then don't repeat that in your query

    on the other hand, if you did decide to do it in the query, you wouldn't need to store the TotalTime field at all

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Feb 2005
    Location
    Ferrara (Italy)
    Posts
    11

    Red face Thank you, but...

    Thank you Maxa and Rudy,
    Probably the problem is in my delphi source code, because the totalTime column was managed as time field type and not as datetime type, I'll modify the source and TotalTime field is (TimeOut - TimeIn) + (timeOut1 - TimeIn1) - LunchTime.
    I prefer to obtain this calculation field throught Delphi source
    Tomorrow I'll try and I'll let you know.

    Tahnk you very much

    Davide

  13. #13
    Join Date
    Dec 2009
    Posts
    3

    sum(totalhrs workedcolumn)

    Hi David
    I know this is an old post but i would be greatful if you could tell me how to calculate the sum of column with values
    totwrkhrs
    12:00
    08:00
    06:00

    i require the sum of totwrkhrs column
    thanking in advance
    regards
    oracleuser


    Quote Originally Posted by davidus View Post
    Thank you Maxa and Rudy,
    Probably the problem is in my delphi source code, because the totalTime column was managed as time field type and not as datetime type, I'll modify the source and TotalTime field is (TimeOut - TimeIn) + (timeOut1 - TimeIn1) - LunchTime.
    I prefer to obtain this calculation field throught Delphi source
    Tomorrow I'll try and I'll let you know.

    Tahnk you very much

    Davide

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    DECLARE @x table (
       totwrkhrs datetime
    )
    
    INSERT INTO @x (totwrkhrs) VALUES ('12:00')
    INSERT INTO @x (totwrkhrs) VALUES ('08:00')
    INSERT INTO @x (totwrkhrs) VALUES ('06:00')
    INSERT INTO @x (totwrkhrs) VALUES ('06:12')
    
    SELECT totwrkhrs
    FROM   @x
    
    SELECT DateDiff(mi, 0, totwrkhrs) As time_in_minutes
    FROM   @x
    
    SELECT Sum(DateDiff(mi, 0, totwrkhrs)) As total_minutes
    FROM   @x
    
    SELECT total_minutes
         , total_minutes / 60 As hours
         , total_minutes % 60 As mins
    FROM   (
            SELECT Sum(DateDiff(mi, 0, totwrkhrs)) As total_minutes
            FROM   @x
           ) As derived_table
    
    SELECT DateAdd(mi, mins, DateAdd(hh, hours, 0)) As total_as_a_datetime_value
    FROM   (
            SELECT total_minutes / 60 As hours
                 , total_minutes % 60 As mins
            FROM   (
                    SELECT Sum(DateDiff(mi, 0, totwrkhrs)) As total_minutes
                    FROM   @x
                   ) As derived_table
           ) As another_derived_table
    Any good for ya?
    George
    Home | Blog

  15. #15
    Join Date
    Dec 2009
    Posts
    3

    Question

    Hi all
    I am giving a piece of my code

    SELECT dif.EMPLOYEE_NUMBER Employee#, dif.FULL_name EmployeeName,
    TO_CHAR(START_DATE,'dd-Mon-rrrr') DOJ ,dif.DEPT_NAME,POSITION , DATE_ENTRAY AttendanceDate,

    to_char(DATE_ENTRAY,'DY') day, TO_CHAR(LNE1,'hh24:mi')TimeIn1 ,TO_CHAR(LNE2,'hh24:mi')TimeOut1,

    TO_CHAR(LNE3,'hh24:mi')TimeIn2 ,TO_CHAR(LNE4,'hh24:mi')TimeOut2, TO_CHAR(LNE5,'hh24:mi')TimeIn3 ,

    TO_CHAR(LNE6,'hh24:mi')TimeOut3, to_char(decode(LNE8,null ,

    decode(LNE7,null, decode(LNE6,null, decode(LNE5,null, decode(LNE4,null, decode(LNE3,null,

    decode(LNE2,null,LNE2 ,LNE2 ),LNE3 ),LNE4 ),LNE5 ),LNE6 ),LNE7 ),LNE8 ),'hh24:mi')TimeOuts ,

    ACT_HOUR Work_Hrs,

    decode(DLY_ABSENT_TYPE,'Late',DED_ABS ,'Late (Deduction)',DED_ABS,'00:00') Late_Hrs,ACT_OVT Over_Time
    FROM jjj_PUNCH_DATA_EMP_LIST trn ,
    jjj_emp_def dif,jjj_PUNCH_CARD_ELEG ele WHERE trn.EMPLOYEE_NUMBER =dif.EMPLOYEE_NUMBER and
    ele.EMPLOYEE_NUMBER =trn.EMPLOYEE_NUMBER and DATE_ENTRAY between '23-Aug-2009' and '24-Aug-2009'

    I require to find the sum of column wrk_hrs
    Act_hour or wrk_hrs belongs to table jjj_PUNCH_DATA_EMP_LIST trn , and the datatype of act_hour is varchar(10 byte)
    the values of column act_hrs, (i require the sum of this column)
    08:00
    07:22
    06:08

    kindly help

    thanking in advance,
    regards
    oracleuser

Posting Permissions

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