Search code examples
databasehibernatehql

Get records with HQL query between two dates

I'm trying to get an affordable car in the time period

SELECT c FROM Car c WHERE c NOT IN (SELECT r.car from Rental r WHERE r.startDateTime >= :startDate AND r.endDateTime <= :endDate)

This query is invalid. For exmaple, my endDate my be more than r.endDateTime

I'm trying to fix this with BETWEEN, but my request is wrong

SELECT c FROM Car c WHERE c NOT IN (SELECT r.car FROM Rental r WHERE :startDate OR :endDate BETWEEN r.startDateTime AND r.endDateTime)

Solution

  • Your query should look like this:

    SELECT c FROM Car c 
WHERE c NOT IN (SELECT r.car FROM Rental r 
                WHERE (:startDate BETWEEN r.startDateTime AND r.endDateTime)
                   OR (:endDate BETWEEN r.startDateTime AND r.endDateTime))