Distinct of date

Question

How to find distinct of date in sql 2000?

For example :i have a table "Dates".It contains different dates like 26-11-2009, 25-11-2009,26-11-2009.'26-11-2009' has two entries.When i select date from this table i need only two entries such as 25-11-2009 and 26-11-2009.

Edited section:

In the table 'Emplyoee' there are two fields-JoinDate and EmployeeName.

All Data contains in Emplyoee table is as follows:

JoinDate      | EmployeeName
------------------------
02-12-2009   Vijay

03-12-2009   Binoy

03-12-2009   Rahul

My select query is as follows:

SELECT DISTINCT JoinDate,EmployeeName FROM Emplyoee

I got the Result as follows:

JoinDate   | EmployeeName
------------------------
02-12-2009   Vijay
03-12-2009   Binoy
03-12-2009   Rahul

But i need the result as follows:

JoinDate   | EmployeeName
------------------------
02-12-2009   Vijay
03-12-2009   Binoy(first employee joined on this date)

Answer 1

SELECT DISTINCT YourDateTimeField
FROM dbo.YourTable

although; I'm guessing you want something like this:

SELECT DISTINCT CONVERT(DATETIME, CONVERT(CHAR(10), YourDateTimeField, 101))
FROM dbo.YourTable

If this is a large table, or if the double conversion would significantly slow things down, you'd want to look for another solution

Answer 2

Having no idea exactly what you require, i can only guess that you need the distinct date values from a filed containg datetime values including times.

So you can try this.

SELECT DISTINCT DATEADD(dd,0, DATEDIFF(dd,0,DateVal)) FROM YourTable

Answer 3

Jim B's answer describes how to get list where every date occurs exactly once.

Or maybe you need dates that occur only once in your table. Your question is unclear.

select your_date_field
from your_table
group by your_date_field
having count(1) = 1;

Answer 4

SELECT DISTINCT DateAdd(dd, DateDiff(dd, 0, MyDateField),0)
FROM MyTable

This will give you the unique dates for your table