SQL - Query with date only from DateTime Field

Hi there,

I have a table that contains the date and time that an employee came to work.  the table is populated everyday, and the time a person came in varies daily.  I want to create a query that will pass only the date, and I want it to return all the employees that came in on that day, regardless of what time it was.  At the moment that field is of type DateTime.

Here is my query:

Select DISTINCT Emp.Name + ' '+ Emp.Surname As FullName, RC.ClockIn
FROM Emp Employees, ClockIns RC
WHERE Emp.Emp_Id = RC.Emp_ID  AND RC.ClockIn = '2010/10/21'

At the moment, it displays no result, but there should be at least one record here.  ClockIn is of type DateTime.
NerishaBAsked:
Who is Participating?
 
Rikin ShahMicrosoft Dynamics CRM ConsultantCommented:
Select DISTINCT Emp.Name + ' '+ Emp.Surname As FullName, RC.ClockIn
FROM Emp Employees, ClockIns RC
WHERE Emp.Emp_Id = RC.Emp_ID  AND RC.ClockIn >= '2010/10/21 00:00:00.000' AND RC.ClockIn <= '2010/10/21 23:59:59.000'
0
 
Imran Javed ZiaConsultant Software Engineer - .NET ArchitectCommented:
Hi,
Please use following query

Select DISTINCT Emp.Name + ' '+ Emp.Surname As FullName, RC.ClockIn
FROM Emp Employees, ClockIns RC
WHERE Emp.Emp_Id = RC.Emp_ID  
AND convert(varchar, RC.ClockIn, 101) = '2010/10/21'

Thanks
0
 
NerishaBAuthor Commented:
Thanks, but that still does not display any result.  
0
 
Imran Javed ZiaConsultant Software Engineer - .NET ArchitectCommented:
sorry use date as
'mm/ddyyyy'

Select DISTINCT Emp.Name + ' '+ Emp.Surname As FullName, RC.ClockIn
FROM Emp Employees, ClockIns RC
WHERE Emp.Emp_Id = RC.Emp_ID  
AND convert(varchar, RC.ClockIn, 101) = '10/21/2010'
0
 
NerishaBAuthor Commented:
Thanks, they both worked.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.