Display all data which are between these dates

So currently, I am trying to display a list of items which are not between date X and date Y. Let us assume that book_date_start = 2021-06-15, and bookdate_end = 2021-06-20

This is my current query:

$query->select('fac_query')
->from('facility_booking')
->where('book_date_start', '>=', $newstartdate)
->where('book_date_end', '<=', $newenddate);

So based on this

$newstartdate = 2021-06-13 and $newenddate = 2021-06-19 (Returns 0 records)
$newstartdate = 2021-06-21 and $newenddate = 2021-06-25 (Returns 0 records)
$newstartdate = 2021-06-11 and $newenddate = 2021-06-12 (Returns 0 records)
$newstartdate = 2021-06-13 and $newenddate = 2021-06-25 (Returns 1 records)
$newstartdate = 2021-06-17 and $newenddate = 2021-06-19 (Returns 0 records)

So basically as I was writing the above out, I realized that my query is WRONG, and since 2021-06-13 is less than 2021-06-15 (true), and 2021-06-20 is more than 2021-06-25 (true) also, that’s why it’s returning the record.

My question: How would I write query if I only want to return the record is the chosen date is between 2021-06-15 to 2021-06-20? (I’m pretty sure I’m overthinking this) but I’m SOO lost. I don’t need the full query or answer, I just hope someone can point me in slightly the right direction and I’ll be good to go.. Thanks all for reading.

Answer

Try the query as below.

$start_date = Carbon::parse($newstartdate)->format('Y-m-d H:i:s');
$end_date = Carbon::parse($newenddate)->format('Y-m-d H:i:s');

$bookinks = DB::table('facility_booking')->where([['book_date_start','<=',$start_date],['book_date_end','>=',$end_date]])
              ->orwhereBetween('book_date_start',array($start_date,$end_date))
             ->orWhereBetween('book_date_end',array($start_date,$end_date))->get();