Check availability for vehicle

Question

I have 3 tables to register a rental for a vehicle.

The first one is where I insert a solicitation with date_out and date_return

Table_Solicitation(Id_Solicitation, date_out, date_return)

Other is where I register my vehicles

Table_Vehicle(Id_Vehicle, description)

And last the table to approve or not

Table_Approval(Id_Approval, Id_Solicitation, Approved, Id_Vehicle)

Now I need to return all vehicles available for a given date_out and date_return, but I'm having problems when these dates are null in the sql because, I think, the left joins.

The sql is

*select V.Id_Vehicle
from Table_Vehicle V
left join Table_Approval TA on (V.Id_Vehicle = TA.Id_Vehicle)
left join Table_Solicitation TS on (TS.Id_Solicitation= TA.Id_Solicitation)
where 
(to_date('$dateOut', 'dd/mm/yyyy hh24:mi') NOT BETWEEN TS.date_out AND TS.date_return) 
AND
(to_date('$dateReturn', 'dd/mm/yyyy hh24:mi') NOT BETWEEN TS.date_out AND TS.date_return) 
AND
(TS.date_out NOT BETWEEN to_date('$dateOut', 'dd/mm/yyyy hh24:mi') AND to_date('$dateReturn', 'dd/mm/yyyy hh24:mi'))*

Now I have these data

Solicitation 1: from 15/03/2014 13h to 15/03/2014 15h (vehicle 1 is confirmed)
Solicitation 2: from 14/03/2014 13h to 14/03/2014 15h (vehicle 1 is available at the combo box, which is correct).

Solicitation 3: from 14/03/2014 12h to 14/03/2014 14h (vehicle 1 is available at the combo box, but it should not, because conflicts with solicitation 2)

Solicitation 4: from 14/03/2014 12h to 16/03/2014 14h (vehicle 1 is available at the combo box, but it should not, because conflicts with solicitation 1)

What am I doing wrong with the between clause in the sql?

Answer 1

You say the problem is due to the null value of the date. Why don't you just "isnull()" the date and then you can compare. See https://stackoverflow.com/a/3524035/1662973 for the oracle variation of isnull, where you can simply then give it a random value, just so your query can properly compare, i.e. make the date 1/1/1900.