select records which are repeated twice!

I have a table in SQL Server with these records :

ID         Date     Time
--     ---------    ----
1      09/05/02     6:00
2      09/05/02     8:00
3      09/05/03     6:00开发者_如何学C
4      09/05/04     8:00
5      09/05/04     6:00

I would like to select those ID who have records in a day which are repeated twice or multiple of two.

How would I do this in SQL Server?

Something like:

select table_1.id, table_1.date from
table as table_1 inner join table as table_2 on 
table_1.date = table_2.date 
and 
table_1.id <> table_2.id

should work alright.

this query just select records with ID=1 and days which are repeated twice or multiple of 2 :

SELECT  *
FROM     MyTable
WHERE  (Date IN
          (SELECT  Date
           FROM  MyTable
           WHERE  (ID = 1)
           GROUP BY Date
          HAVING  (COUNT(Date) % 2 = 0)
          )
       )
 AND (ID = 1)

this will return the expected result

     declare @aa table (id int,datee date)
     insert into @aa 
    select 1, '09/05/02' union all
     select 2, '09/05/02' union all
    select 3, '09/05/03' union all
    select 4, '09/05/04' union all
    select 5, '09/05/04'    

     select * from @aa where datee in (
                select datee from @aa group by datee having COUNT(datee)>1)

Edit

CREATE TABLE MyTable(ID INTEGER, Date DATETIME)

INSERT INTO MyTable VALUES (1, '09/05/02 6:00')
INSERT INTO MyTable VALUES (1, '09/05/02 8:00')
INSERT INTO MyTable VALUES (2, '09/05/03 6:00')
INSERT INTO MyTable VALUES (3, '09/05/04 8:00')
INSERT INTO MyTable VALUES (4, '09/05/04 6:00')

SELECT  t1.*
FROM    MyTable t1
        INNER JOIN (
          SELECT  t1.ID, Date1 = t1.Date, Date2 = t2.Date
          FROM    MyTable t1
                  INNER JOIN MyTable t2 ON CAST(t2.Date AS INTEGER) = CAST(t1.Date AS INTEGER)
                                           AND DATEPART(HOUR, t2.Date) = DATEPART(HOUR, t1.Date) - 2
                                           AND t2.ID = t1.ID
        ) t2 ON t2.ID = t1.ID AND t1.Date IN (t2.Date1, t2.Date2)

It's not quite clear to me from your layout whether the date and the time are in separate columns, or just one. That is, do you have

ID    theDate    theTime
--    --------   -------
1     09/05/02   6:00
1     09/05/02   8:00
2     09/05/03   6:00
3     09/05/04   8:00
4     09/05/04   6:00

or

ID    theDateTime
--    -------------
1     09/05/02 6:00
1     09/05/02 8:00
2     09/05/03 6:00
3     09/05/04 8:00
4     09/05/04 6:00

If the first is true, as it seems, then Ramesh has nearly, but not quite, the correct answer:

select *
from theTable
where theDate in (
    select theDate 
    from theTable
    group by theDate
    having count(theDate) % 2 = 0
)

If the second is true, your job is significantly more complex - I'll take a think about it.