|Subject||Select based upon date between two date fields|
I am new to firebird so excuse the possibly stupid question.
I am trying to select based upon a few criteria, one being that a date passed in '9/15/2014' be between the values of two date fields in the firebird table. Those would be fromdatetime and todatetime.
The values in the database for those two fields respectively are '09/01/2014' and '10/23/2014'
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