Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Date Comparison Error Expand / Collapse
Author
Message
Posted Sunday, September 30, 2012 11:33 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 10, 2013 6:03 AM
Points: 35, Visits: 14
Dear All,

Greetings!!!!

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
Post #1366280
Posted Monday, October 01, 2012 3:10 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 9:43 AM
Points: 316, Visits: 1,022
Cannot see any problem with this. Can you look at supplied code & explain how yours differs.

Thanks


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


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


Post #1366408
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse