laravel query where not between is not working

Question

I have to search halls for booking with given dates and time (morning and evening). How i can find available dates from data with laravel query. If i search dates 11-02-2019 to 27-02-2019 .It should only return 'These dates are not available!'. In this query it is returning booking 3. What's wrong with this query? I have posted database image.

public function getAvailableEvents(Request $request)
{
    try {
        $allInputs = Input::all();
        $categoryID = $request->input('category_id');
        $startDate = $request->input('start_date');
        $endDate = $request->input('end_date');
        $time = $request->input('time');

        $validation = Validator::make($allInputs, [
            'category_id' => 'required',
            'start_date' => 'required',
            'end_date' => 'required',
            'time' => 'required',
        ]);
        if ($validation->fails()) {
            DB::rollback();
            return $response = (new apiresponse())->customResponse('Fields required!',
                422,
                $validation->errors()->toArray());
        } else {

            $getEvents = Booking::where('category_id', '=', $categoryID)
            ->where('booking_time', '!=', $time)
            ->where('date_from', '!=', $startDate)
            ->where('date_to', '!=', $endDate)
            ->orWhere('date_from', '<', $startDate)
            ->orWhere('date_to', '>', $endDate)
            ->whereNotBetween('date_from', [$startDate,$endDate])
            ->whereNotBetween('date_to', [$startDate,$endDate])
            ->whereRaw("? NOT BETWEEN date_from AND date_to", [$startDate, $endDate])
            ->get();
           // dd($getEvents);

            if (count($getEvents) > 0) {
                $data = array();
                for ($i = 0; $i < count($getEvents); $i++) {
                    $data[] = array(

                        "booking_id" => $getEvents[$i]->id,
                        "ref_no" => $getEvents[$i]->ref_no,
                        "category_id" => $getEvents[$i]->category_id,
                        "event_id" => $getEvents[$i]->event_id,
                        "user_id" => $getEvents[$i]->user_id,
                        "phone" => $getEvents[$i]->phone,
                        "booking_status" => $getEvents[$i]->booking_status,
                        "type" => $getEvents[$i]->type,
                        "date_from" => $getEvents[$i]->date_from,
                        "date_to" => $getEvents[$i]->date_to,
                        "booking_time" => $getEvents[$i]->booking_time,
                        "price" => $getEvents[$i]->price,
                        "rating" => $getEvents[$i]->rating,
                        "date_time" => $getEvents[$i]->date_time ?? "",

                    );
                }
                return $response = (new apiresponse())->customResponse('Events found!',
                    200,
                    $data);

            } else {
                DB::rollback();
                return $response = (new apiresponse())->customResponse(
                    'These dates are not available!',
                    422,
                    (object)[]);
            }
        }

    } catch (\Illuminate\Database\QueryException $ex) {
        return $response = (new apiresponse())->customResponse(
            'Fail!',
            422,
            $ex);
    }
}

Answer 1

The less than / greater than comparisons aren't going to work reliably with a varchar data type on the date columns.

If you can, change the column type to DATE (or similar Date-specific type, whatever's appropriate for your application). Then the comparisons will work as expected.

If you can't, you can use the STR_TO_DATE() function to convert to dates and then compare.