date comparison issue with < and <= operator

Question

Post reply

date comparison issue with < and <= operator

jamesmario

SSC Veteran

Points: 254
More actions
December 17, 2010 at 7:54 am

#232541

i have 5 records in an table. one of the colum is datetime type. while input date compares with existing date using < and <= operators doesn't yield the same result.
For example,
Table 1:
Name createdate
Test1 12/01/2010
Test2 12/02/2010
Test3 12/03/2010
Test4 12/04/2010
Test5 12/05/2010
QUERY:
SELECT Count(*) FROM TABLE1 WHERE Createdate <= '12/05/2010'
Returns count as 4
SELECT Count(*) FROM TABLE1 WHERE Createdate < '12/06/2010'
Returns count as 5
Is this a bug in sql query itselft or any issues with my query?
appreciate your help.
thanks.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95461 More actions · Answer 1

This code:

SET NOCOUNT ON;

DECLARE @test-2 TABLE (Name char(5), createdate datetime);

INSERT INTO @test-2

SELECT 'Test1', '12/01/2010' UNION ALL

SELECT 'Test2', '12/02/2010' UNION ALL

SELECT 'Test3', '12/03/2010' UNION ALL

SELECT 'Test4', '12/04/2010' UNION ALL

SELECT 'Test5', '12/05/2010';

SELECT Count(*) FROM @test-2 WHERE Createdate <= '12/05/2010';

SELECT Count(*) FROM @test-2 WHERE Createdate < '12/06/2010';

produces these results:

-----------

5

-----------

5

As you can see, it's working correctly.

Now, is it possible that you have times stored with those dates, especially the one for 12/5? If so, then the implicit conversion of '12/5/2010' becomes '12/5/2010 00:00:00.000', and if you have any time after midnight then you won't get a count on that record.

Edit: if you do have a time, and are interested in dates on that date, then you have just stumbled on to why many folks break out datetimes in the where clauses to:

DECLARE @StartDate datetime,

@EndDate datetime;

SET @StartDate = '20101201';

SET @EndDate = '20101206';

SELECT...

WHERE DateField >= @StartDate

AND DateField < @EndDate

Note that the end date is for the day AFTER what we're looking for (with a time of 00:00:00.000), and we looking for dates that are LESS THAN that value - this will find any datetime value up to 12/5/2010 23:59:59.997 (and in SQL 2008, if using the datetime2 datatype, up to 23:59:59.999999999).

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2