none
Adding datetime fields

    질문

  • Hi

    I'm learning SQL, stuck on a problem, and would be very grateful if someone could point me in the right direction please.

    I have a table that contains employee overtime data.  The table contains the employee ID number, the work week ID, basic hours, and overtime hours worked.
    What i want to do is SUM(OThrs) for a particular employee to get the total OT hours worked in a given workweek.

    However, as I understand it the datetime datatype stores its value as a value measured from a base date of Dec-30-1899.  As it wouldn't make sense to add the datetime fields due to this, is there any way around it? 

    The OThrs is brought in from a csv file through a DTS package and is in the format of.... eg 07:45, 13:20, 02:12, 08:10

    So if those times above were all for the same employee in the same work week, it would total 31:27

    I'd be grateful for some poiters on this problem.
    Thanks & Regards
    MartyT

    2005년 9월 21일 수요일 오전 10:22

답변

  • If you did not specify any date part in the datetime value, then the date portion will default to 1900-01-01. Confirm that this is the case for those values. Assuming this condition, you can do the following:

    select t.EmployeeId, t.WorkWeekId,
    convert(varchar(5), dateadd(minute, sum(datediff(minute, '', t.Othrs)), ''), 114) as total_ot_hours
    from tbl as t
    group by t.EmployeeId, t.WorkWeekId


    Note that the above query only has resolution less than 24 hrs. If you need more than that, then take the minute value directly and generate the hour/minutes part yourself.
    2005년 9월 21일 수요일 오후 8:22

모든 응답

  • If you did not specify any date part in the datetime value, then the date portion will default to 1900-01-01. Confirm that this is the case for those values. Assuming this condition, you can do the following:

    select t.EmployeeId, t.WorkWeekId,
    convert(varchar(5), dateadd(minute, sum(datediff(minute, '', t.Othrs)), ''), 114) as total_ot_hours
    from tbl as t
    group by t.EmployeeId, t.WorkWeekId


    Note that the above query only has resolution less than 24 hrs. If you need more than that, then take the minute value directly and generate the hour/minutes part yourself.
    2005년 9월 21일 수요일 오후 8:22
  • That's a big help.  Thanks for your time - much appreciated
    2005년 9월 21일 수요일 오후 9:30