CODEWITHSUNDEEP
databasehibernatehql
sohhe
sohhe

Reputation: 23

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)

Upvotes: 0

Views: 51

Answers (1)

Simon Martinelli
Simon Martinelli

Reputation: 36133

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

Upvotes: 1

Related Questions