Employee Name & Hours Worked for The Day

Discussion in 'Microsoft Point Of Sale' started by Jerry, May 25, 2008.

  1. Jerry

    Jerry Guest

    Hi all,

    I'm trying to determine the name and time that employees work on a
    certain day. So I'd like to see the employee name next to the hour of
    the day like this:

    Name, TimeIn, TimeOut
    Yvonne, 11, 15

    select c.name, datepart([hour], t.TimeIn) as TimeIn, datepart([hour],
    t.TimeOut) as TimeOut
    --select c.name, t.timein, timeout
    from cashier c, timeclock t
    where t.cashierID = c.id
    and convert(varchar(10),t.DBTimeStamp,110) = '05-05-2008'

    I have trouble with the date convert me thinks. Not too familiar with
    date format conversions in SQL Server.

    Any suggestions?
     
    Jerry, May 25, 2008
    #1
    1. Advertisements

  2. Jerry

    convoluted Guest

    Hi Jerry -

    Can something like this work for you?

    select c.name, datepart([hour], t.TimeIn) as Hour_TimeIn, datepart([hour],
    t.TimeOut) as Hour_TimeOut
    from cashier c inner join timeclock t on c.id = t.cashierid
    where t.timein between '1/3/2008' and '1/4/2008'

    -- this would show you at what hour clock ins and clock outs took place (for
    january 3 2008) -- hope this helps....
     
    convoluted, May 26, 2008
    #2
    1. Advertisements

  3. Jerry

    Jerry Guest

    This works. So how do I convert datepart to have the dd/mm/yyyy and
    the hh? I really want it to be

    05/13/2008 10:00
    05/13/2008 11:00

    for the time format.
     
    Jerry, May 28, 2008
    #3
  4. Jerry

    convoluted Guest

    Try it without converting the datatype....you'll get date and hr/min on both
    the clock in and the clock out - hope this helps.....

    select c.name, t.TimeIn, t.TimeOut from cashier c inner join timeclock t on
    c.id = t.cashierid where t.timein between '1/3/2008' and '1/4/2008'

     
    convoluted, May 28, 2008
    #4
  5. Jerry

    Jerry Guest

    I only want the hour, I don't want minutes or seconds.

    05/13/2008 10
    05/13/2008 11
     
    Jerry, May 29, 2008
    #5
  6. Jerry

    convoluted Guest

    OK - try it this way - though I split it into two columns since I'm not sure
    how to "cut off" the minutes/seconds from the datetime datatype...maybe a
    more experienced SQL user will post that solution for you with a "right trim"
    - hope this helps....

    select c.name, convert(char(11),t.timein) as DateIn, datepart([hour],
    t.TimeIn) as Hour_TimeIn, convert(char(11),t.timeout)as DateOut,
    datepart([hour], t.TimeOut) as Hour_TimeOut from cashier c inner join
    timeclock t on c.id = t.cashierid where t.timein between '1/3/2008' and
    '1/4/2008'



     
    convoluted, May 29, 2008
    #6
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.