Post reply

Working only With Time in Sql?

  • November 29, 2005 at 6:11 am

    #88583

     have some confusion. Scenario is that for an Employee to start and end job the following fields are available;

    Field                  DataType

    =====               ========

    Emp_Id               Foreign Key}

    pkdt                   DateTime PrimaryKey 

    CheckInTime        DateTime

    CheckOutTime      DateTime

    InOutStatus         bit

    HoursWorked       ?---> What shoulud be DataType?

    When an Employee Checks in All fields are set and InOutStatus is set to 1 Except CheckOutTime and HoursWorked.When The Employee checks out InOutStatus is set to 0,CheckOutTime is set and then i have to calculate the HoursWorked (TimeDuaration). Constarins are CheckOutTime is

    always greater than CheckInTime.(There may be different dates for them as employee gets check in ,Nov 29,2005 on time 17:00 and checksout , Nov 30,2005 on time 03:30).Now according to calculation he worked for 10 hours and 30

    minutes .How to calculate this hours worked(only time duration and to which datatype it should be saved in sql) as well as the Stored Procedure for this whole process.Whenever Employee comes his Emp_Id is only known.

    Thnx in Advance.

  • November 29, 2005 at 6:58 am

    #606397

    As you may have found out: DateDiff is useless for your problem as it only compares a part of the the date/time.  Fortunately I was able to figure this out:

    Use a datetime datatype for your hours worked, calcuate it by simple subtraction Time stopped - time started, and then CAST the resulting difference as a float and multiply it appropriately to convert from days to hours, to minutes or seconds.

    Use the following code to test your question:

    DECLARE @sd datetime, @ed datetime

    SELECT @sd = '2005-11-29 17:00:00.917'

    SELECT @ed = '2005-11-30 03:30:12.817'

    SELECT @sd, @ed

    SELECT CAST(@ed - @sd AS FLOAT), CAST(@ed - @sd AS FLOAT) * 24 AS HOURS

    , CAST(@ed - @sd AS FLOAT) * 24 * 60 AS MINUTES

    , CAST(@ed - @sd AS FLOAT) * 24 * 60 * 60 AS Seconds

    Good luck,

    Brian

  • November 30, 2005 at 6:12 am

    #606686

    i would suggest

    SELECT DATEDIFF(mi, CheckInTime, CheckOutTime) AS MinutesWorked

    the datatype would be integer and then when you are displaying the hours worked in a report you convert to hours and minutes

    Everything you can imagine is real.

Viewing 3 posts - 1 through 2 (of 2 total)

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