How do I check that dates from one range are not in another range?

Question

I have such the task: there are assets (computer, printer and so on) that departments can reserve for themselves. Naturally, booking dates can't overlap. I wrote the method that accepts a set assets and date. I Check this date with the dates of already existing reservations and, if it falls into an already active reservation, then I throw an error to the user that the date is busy. If it doesn't fit, I'll reserve it for that date. The problem is that method is currently not working correctly and doesn't take into account all the options. For example, department has reserved an asset from 27.10.2022 to 27.10.2022. While another department will be able to reserve the same asset from 26.10.2022 to 28.10.2022. This logic is wrong.

Please help me implement this method correctly.

public boolean isDateWithinRangeAssetReservations(Set<Asset> assets, Date checkDate) {

    Date checkDateWithTruncate = DateUtils.truncate(checkDate, Calendar.DAY_OF_MONTH);

    String query = "select e from am_AssetReservation e where e.refAsset IN :assets and (:checkDate between e.dateFrom and e.dateTo) and e.isActive = true";
    List<AssetReservation> assetReservations = dataManager.load(AssetReservation.class)
            .query(query)
            .parameter("assets", assets)
            .parameter("checkDate", checkDateWithTruncate)
            .list();
    return !assetReservations.isEmpty();

Above is the implementation of my method whose logic is not working properly

Answer 1

I would suggest to rewrite query to use >= and <= conditions, instead of "between". Also, change the "eclipselink.sql" logger to DEBUG level to view real SQL issued to the database. It will make the situation clear.