How to compare date in SQL Server 2008

Question

Here I need to compare date using SQL Server 2008. I want show details of the table if the specific date appears more than one time.

Example:

create table datetest
(
  columnx nvarchar(10),
  columny nvarchar(10),
  coldate date,
  coltime datetime
)

insert into datetest values('a123','b123','2014-01-01','01:05:00.000');
insert into datetest values('a123','b456','2014-01-01','02:15:00.000');
insert into datetest values('a123','b789','2014-01-01','03:25:00.000');
insert into datetest values('a321','b321','2014-02-03','10:05:00.000');
insert into datetest values('a243','b243','2014-03-04','11:05:00.000');
insert into datetest values('a243','b243','2014-03-04','12:05:00.000');
insert into datetest values('a243','b243','2014-03-04','12:05:00.000');

Now I need to show only that records whose dates are greater than one appear in the table.

Expected result:

columnx    columny   coldate         coltime 
-------------------------------------------------------
a123        b123    2014-01-01  1900-01-01 01:05:00.000
a123        b456    2014-01-01  1900-01-01 02:15:00.000
a123        b789    2014-01-01  1900-01-01 03:25:00.000
a243        b243    2014-03-04  1900-01-01 11:05:00.000
a243        b243    2014-03-04  1900-01-01 12:05:00.000
a243        b243    2014-03-04  1900-01-01 12:05:00.000

My attempt:

select * 
from datetest 
where coldate = '2014-01-01' or coldate = '2014-03-04';

Note: But this is not the right way for the large records to check.

Answer 1

try like this

select * from datetest
where coldate in (select coldate from 
datetest group by coldate having count(*) > 1)

DEMO