Manipulating Employees Punchtimes

  • I have a table that keps tracks of employees punch ins and outs.

    CREATE TABLE [EmployeePunch] (

     [scalldate] [varchar] (20) NULL ,

     [sEmployeeId] [varchar] (20) NULL ,

     [TotalHr] [numeric] (10,4) NULL ,

     [sLogin] [varchar] (20) NULL ,

     [sLogout] [varchar] (20)  NULL

    )

    INSERT [EmployeePunch]  (scalldate, sEmployeeId, TotalHr, sLogin)

    VALUES ('20070227', '123', 8.5, '09:00:00')

    INSERT [EmployeePunch]  (scalldate, sEmployeeId, TotalHr, sLogin)

    VALUES ('20070227', '456', 6.256, '09:00:00')

    )

    I have a file that needs to be loaded into that table.

    The file contains sCalldate, sEmployeeId, totalhr, and slogin.

    Ex:

    20070227, 123, 8.5, 09:00:00

    Does anyone know how to add 8.5 to 09:00:00 to get 17:30:00???

     

  • if you can, redesign your data storage... you should use a date time filed to store the date and the time together. if you need them separate, you can always pull them out formatted, but they should not be varchar columns. something like at the bottom this would be better:

     my firststep was to merge your data back into a datetime field.

    select convert(datetime, scalldate + ' ' + slogin) as punchtime,* from EmployeePunch

    select dateadd(hour,8.5,convert(datetime, scalldate + ' ' + slogin)) as punchtime,* from EmployeePunch

    select datediff(minute,convert(datetime, scalldate + ' ' + slogin),convert(datetime,sLogout) )

    from EmployeePunch

     

    my suggested redesign:

     CREATE TABLE [EmployeePunch2] (

     [sEmployeeId] [varchar] (20) NULL ,

     [sLogin] datetime NULL ,

     [sLogout] datetime  NULL,

     [TotalHr] AS datediff(minute,sLogin, sLogout)  / 60.00

    )

    INSERT [EmployeePunch2]  ( sEmployeeId, sLogin, sLogout)

    VALUES ('123', '02/27/2007 09:00:00','02/27/2007 17:30:00')

    INSERT [EmployeePunch2] ( sEmployeeId, sLogin, sLogout)

    VALUES ( '456' ,'02/27/2007 09:00:00','02/27/2007 13:25:35')

    select * from EmployeePunch2

    results:

    sEmployeeIdsLoginsLogoutTotalHr
    1232007-02-27 09:00:00.0002007-02-27 17:30:00.0008.500000
    4562007-02-27 09:00:00.0002007-02-27 13:25:35.0004.416666

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Hi ,

    Try This one!!!!

    select  convert(varchar,dateadd(mi,(8.5 * 60), convert(datetime,'09:00:00',108)),108)

     

    Regrads ,

    Amit G.

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

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