Selecting BETWEEN Dates

[dfs8463]

Hi
I have a problem selecting records from a transaction table for a specific period. The query goes like this:

DECLARE @d1		datetime
	,@d2		datetime

	SET @d1		= '1/Jan/2012' 
	SET @d2		= '11/Jan/2012'

	SELECT DISTINCT BuyerName
	FROM TransactionsList WITH (nolock) 
	WHERE trnDate BETWEEN @d1 AND @d2

The table is very large, but 'trnDate' column is indexed.
This results in 150 records out of a total of 113000 similar records.
The problem is that it takes forever. (I gave up after a minute)

However when I tried the same thing, but hard-coding the dates, like so,

DECLARE @d1		datetime
	,@d2		datetime

	SET @d1		= '1/Jan/2012' 
	SET @d2		= '11/Jan/2012'

	SELECT DISTINCT BuyerName
	FROM TransactionsList WITH (nolock) 
	WHERE trnDate BETWEEN '1/Jan/2012' AND '11/Jan/2012'

the result took only 100ms.

Is there anything that I am missing, should change or add? I hope that the problem and example are explained clearly.

Thanks
-dfs-

 

[johnherman]

My return question: What is the datatype of trnDate? If it is DATE, perhaps the extra time is implicit conversions for your declared variables, which are datetime to the datatype of trnDate.

==================================
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[dfs8463]

Hi John
Thank you for replying
The trnDate column is datetime, and there are never any NULL values.

I tried again, leaving the code as is for reference, and, wonder of wonders, it seems to have worked now. To clarify, the code that took forever is now working in a flash, with NO changes made to the database or query.

In the future I will incorporate the time in the date limits, but I am far from convinced that the issue will not repeat itself.

Thanks for your support!
-dfs-