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?

Answer 2

If you need to select by month often, you could consider adding two computed columns to your table - one for the month, one for the year:

ALTER TABLE dbo.YourTable
ADD DatePlacedYear AS YEAR(DatePlaced) PERSISTED

ALTER TABLE dbo.YourTable
ADD DatePlacedMonth AS MONTH(DatePlaced) PERSISTED

Those two new columns are automatically computed by SQL Server, they're persisted (e.g. part of the table's storage), and you can even put an index on them, if that makes sense for you.

With those in place, you could now use a query like:

SELECT (columns)
FROM dbo.YourTable
WHERE DatePlacedYear = 2012 AND DatePlacedMonth = 2

to get all data from February 2012.

It's a classic space-vs-speed trade-off - by storing the two extra columns for each row, you need more space - but in return, querying gets easier and if you have an index on (DatePlacedYear, DatePlacedMonth), your queries should (ideally) be quite fast.

Answer 3

Instead of using AddMilliseconds(-1) try use AddMilliseconds(-3)

See this question how SQL Server treats the milliseconds