linq to sql - Incorrect date comparison results in SQL Server 2008 R2

Question

I have a LINQ 2 SQL query that's getting me a list of results for the month of February 2012. The resulting where clause is

DECLARE @p0 DateTime = '2012-02-01 00:00:00.000'
DECLARE @p1 DateTime = '2012-02-29 23:59:59.999'
....
WHERE (CONVERT(DATE, [t0].[DatePlaced]) >= @p0) AND (CONVERT(DATE, [t0].[DatePlaced]) <= @p1)

When this runs I'm getting results for 3/1/2012 showing up as well as all the results for 2/2012.

If I change the where clause to use BETWEEN then the results only contain dates for February.

WHERE [t0].[DatePlaced] BETWEEN @p0 AND @p1

I'm using .net 4 and SQL Server 2008 R2 with and without SP1.

Switching the dates to 3/1/2011 and my query's end date to '2011-02-28 23:59:59.999' yielded the same results.

Is there another way to get the results for just 2/2012 aside from using BETWEEN which LINQ 2 SQL doesn't support?

Answer 1

.999 rounds up to midnight of the next day. You can check this:

DECLARE @p1 DateTime = '2012-02-29 23:59:59.999';
SELECT @p1;

What do you get?

Instead of trying to figure out the last instant of today (which will be different depending on the data type and precision), what you want instead is an open-ended date range:

DECLARE @p0 DATE = '2012-02-01',
        @p1 DATE = '2012-03-01';
....
WHERE [t0].[DatePlaced] >= @p0
AND [t0].[DatePlaced] < @p1

Even easier would be to just pass in the starting date and say:

DECLARE @p0 DATE = '2012-02-01';

....
WHERE [t0].DatePlaced >= @p0
AND [t0].DatePlaced < DATEADD(MONTH, 1, @p0)

For some elaborate ideas about datetime best practices:

• Bad habits to kick : mis-handling date / range queries

For some info on why BETWEEN (and by extension >= AND <=) is evil:

• What do BETWEEN and the devil have in common?