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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.