Post reply

Working with time part of datetime

  • December 10, 2003 at 5:52 am

    #83680

    I have a column that stores hours worked by eng. the data is stored as datetime

    Eng JobTime

    CDP 1900-01-01 01:00:00.000

    CDP 1900-01-01 02:45:00.000

    CDP 1900-01-01 01:40:00.000

    CDP 1899-12-31 23:00:00.000

    the time portion represents the number of hours worked on a job.

    I want to be able to strip out the time portion and then sum up the hours work. for the above example the answer I need is 28.25 hours.

    Can anyone help me

    Thanks in advance

    Karl

  • December 10, 2003 at 6:24 am

    #485511

    Here is a little example that might help...

    

    -- Show sample time data 

    Select Time,  

    	DateDiff(HH, '1/1/00', Time) % 24 as TheHours,  

    	DateDiff(n, '1/1/00', Time) % 60 as TheMinutes 

    From (select top 4 CrDate as Time From SysObjects order by CrDate desc) A 

     

    -- Calc Hours & Minutes 

    Select Sum(DateDiff(HH, '1/1/00', Time) % 24) as TheHours, 

    	Sum(DateDiff(n, '1/1/00', Time) % 60) as TheMinutes 

    From (select top 4 CrDate as Time From SysObjects order by CrDate desc) A 

    

    -- Put it together 

    Select Sum(DateDiff(HH, '1/1/00', Time) % 24) + Convert(Int, Sum(DateDiff(n, '1/1/00', Time) % 60) / 60) as TheHours, 

    	Sum(DateDiff(n, '1/1/00', Time) % 60) % 60 as TheMinutes 

    From (select top 4 CrDate as Time From SysObjects order by CrDate desc) A



    Once you understand the BITs, all the pieces come together

  • December 10, 2003 at 7:15 am

    #485531

    Thanks. That appears to do the trick

  • December 10, 2003 at 10:09 am

    #485576

    FLOOR(SUM(DATEPART(hh,TimeCol))+SUM(DATEPART(n,TimeCol))/60)+(SUM(DATEPART(n,TimeCol))%60)/100.

    --Jonathan



    --Jonathan

  • December 11, 2003 at 8:00 am

    #485719

    Hi Karl,

    I am glad you could solve the problem. Here is some thought:

    Perhaps it is not the best way to store the number of hours. You will be better off by storing the hours in a separate column. This will make the system simpler to maintain.

    What say you?

    RA

  • December 11, 2003 at 8:04 am

    #485724

    Hi,

    Here is an addendum:

    In my previous post, I meant storing hours in a separate column with a different data type (float or real or whatever) - not as datetime.

    Thanks

    RA

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply