> 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)


Sean