>> The sql is below. It works fine until I add the BETWEEN statement. Hopefully
>> you can point out something simple that I am doing wrong.
>>
>>
>> SELECT description, WOReqd, Addontype as AddonTypeID, RatebookID FROM
>> rentaladdons LEFT JOIN rentalratebook on rentaladdons.ratebookID =
>> rentalratebook.ratebookID WHERE addonID = '1002' and cast('09/15/2014' as
>> date) BETWEEN rentalratebook.fromdatetime and
>> rentalratebook.todatetime
>
>You are using the wrong approach, you need to try:
>
>SELECT
> description, WOReqd, Addontype as AddonTypeID, RatebookID
>FROM rentaladdons
> LEFT JOIN rentalratebook on rentaladdons.ratebookID =rentalratebook.ratebookID
>WHERE
> addonID = '1002'
> and rentalratebook.fromdatetime >= cast('09/15/2014' as date)
> and rentalratebook.todatetime =< cast('09/15/2014' as date)

Why is his approach wrong, Sean? It might be unusual to have the constant on the left side of a comparison, but I'm sure I've done it when it is the easiest way to write a query. I'm pretty sure <date> BETWEEN <date> AND <date> should work regardless of which dates are constant, parameters or field names.

I think his error has nothing to do with the BETWEEN, but rather the reason is that he didn't only add the BETWEEN, he also added the LEFT JOIN and at least RatebookID exists in both tables (probably giving him the ambiguous field name error). He may just have assumed it was the BETWEEN that gave him the error, rather than try the statement without the BETWEEN.

I'd change the query to:

SELECT ra.description, ra.WOReqd, ra.Addontype as AddonTypeID, ra.RatebookID
FROM rentaladdons ra
JOIN rentalratebook rr on ra.ratebookID = rr.ratebookID
WHERE ra.addonID = '1002' AND '09/15/2014' BETWEEN rr.fromdatetime and rr.todatetime

(change ra to rr if I guess wrong which table some fields belong to)

LEFT JOIN is sometimes good to use, but in your case you use fields from rentalratebook also in your WHERE clause, effectively turning the LEFT JOIN into an [INNER] JOIN. [INNER] JOIN gives the optimizer more of a choice regarding which PLAN to generate, so in this query, you're probably better off not using LEFT (LEFT JOINs can sometimes be useful, but this is not one of those cases). Moreover, there's no need to cast 09/15/2014 to date, it doesn't harm doing it, but you don't need to.

One further thing to note, is that you use <date> between <datetime> and <datetime>. 09/15/2014 is between 09/14/2014 00:00:01 and 09/15/2014 23:59:59, but it is not between 09/15/2014 00:00:01 and 09/15/2014 23:59:59. Moreover, I would not trust that it was between 09/15/2014 00:00:00 and 09/15/2014 23:59:59, since I would be uncertain whether 09/15/2014 00:00:00 was an exact value or an approximation that could be slightly less or slightly more than 09/15/2014.

HTH,
Set