--Today I face a problem in Date Comparison please help me in out thanks in advance... 
--problem mention below:-

--In my Table there is a Column Named DNCDATE, format of this Column is DateTime. 
--when i pass a varchar type date then it will not compute any output. 
--e.g DNCDATE>='29 Sep 2012' when i cast the column then it will give me output 
-- but it will take longer time to compute the result e.g. CAST( DNCDATE as DATE)>='29 Oct 2012'..

--Please guide me about the date comparison and help me out...

--Note :- DNCDate Column's Max Value is 2nd Oct 2012, which need to pulled out in our query

--=========== TEST DATA ================

declare @a as table ( DNCDATE DateTime );
insert @a values ( '26 Sep 2012  12:00:00' );
insert @a values ( '27 Sep 2012  12:00:00' );
insert @a values ( '28 Sep 2012  12:00:00' );
insert @a values ( '29 Sep 2012  00:00:00' );
insert @a values ( '29 Sep 2012  01:00:00' );
insert @a values ( '29 Sep 2012  12:00:00' );
insert @a values ( '29 Sep 2012  23:59:59' );
insert @a values ( '30 Sep 2012  12:00:00' );
insert @a values ( '02 Oct 2012  12:00:00' );

--======== QUERY =========

select * from @a
where DNCDATE>='29 Sep 2012';

--======== RESULTS ============
/*
DNCDATE
-----------------------
2012-09-29 00:00:00.000
2012-09-29 01:00:00.000
2012-09-29 12:00:00.000
2012-09-29 23:59:59.000
2012-09-30 12:00:00.000
2012-10-02 12:00:00.000

(6 row(s) affected)
*/

-- Are you using equals:
select * from @a
where DNCDATE='29 Sep 2012';
-- This doesn't work because the data type DateTime includes the time.

-- This will work though:
select * from @a
where DNCDATE >= '29 Sep 2012' AND DNCDATE < '30 Sep 2012';