Employee Name & Hours Worked for The Day


J

Jerry

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?
 
Ad

Advertisements

C

convoluted

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....
 
J

Jerry

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....



Jerry said:
I'm trying to determine the name andtimethat 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?- Hide quoted text -
- Show quoted text -
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.
 
C

convoluted

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'

Jerry said:
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....



Jerry said:
I'm trying to determine the name andtimethat 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?- Hide quoted text -
- Show quoted text -
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.
 
J

Jerry

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'



Jerry said:
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....
:
Hi all,
I'm trying to determine the name andtimethat 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?- Hide quoted text -
- Show quoted text -
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
05/13/2008 11
for the time format.- Hide quoted text -
- Show quoted text -
I only want the hour, I don't want minutes or seconds.

05/13/2008 10
05/13/2008 11
 
Ad

Advertisements

C

convoluted

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'



Jerry said:
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'



Jerry said:
On May 25, 5:01 pm, convoluted <convolu...@discussions.microsoft.com>
wrote:
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....
:
Hi all,
I'm trying to determine the name andtimethat 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?- Hide quoted text -
- Show quoted text -
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
05/13/2008 11
for the time format.- Hide quoted text -
- Show quoted text -
I only want the hour, I don't want minutes or seconds.

05/13/2008 10
05/13/2008 11
 

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. After that, you can post your question and our members will help you out.

Ask a Question

Top