Had a fantastic bug the other day. In fact it's still a mystery as to why this is a problem. But there you go.

Here was the original "Where" clause for an invoice report.

Where (
CASE WHEN IsDate(tblOrder.SentDate) = 0 THEN '19000101'
ELSE CONVERT(DateTime, tblOrder.SentDate, 103) END
BETWEEN CONVERT(DateTime, @FromDate, 103)
AND CONVERT(DateTime, @ToDate, 103)
)
AND ((tblCustomer.customerid = @customerID) or (@customerID is null))

We changed the billing date and ended up with this query..

Where
(
CASE WHEN IsDate(tblOrder.AvailableForBillingDate) = 0 THEN '19000101'
ELSE CONVERT(SmallDateTime, tblOrder.AvailableForBillingDate, 103) END
BETWEEN CONVERT(SmallDateTime, @FromDate, 103)
AND CONVERT(SmallDateTime, @ToDate, 103)
)
AND
(cust.customerid = @customerID) or (@customerID is null)

... only trouble is, while the CustomerID section worked fine T-SQL TOTALLY ignored the first part of the where clause. I mean, totally. At first we couldn't believe it and ran it on a number of servers. But to no affect.

The problem? Well, SentDate was declared as DateTime format while AvailableForBillingDate is defined as a SmallDateTime. But why would create a section of clause to be ignored, that's a realy mystery.

In the end, I employed a trick and included the conditions of the Where clause within the From section.

FROM
inner join ...
tblOrder as ord on ... and
ord.AvailableForBillingDate is not null and
ord.AvailableForBillingDate >= @FromDT and
ord.AvailableForBillingDate <= @ToDT inner join
.....
Where
(cust.customerid = @customerID) or (@customerID is null)

Now SQL responds to the query.

How odd is that.

Tags: SQL Server 2005, SS2005, SQL, T-SQL