Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

Dikurr11's avatar

Query To Shows Data on Date Range

I have database table form like this:

          |ID|Vehicle|Date_start|Date_end|Status|
          |1|A|2021-01-17 12:00:00|2021-01-18 13:00:00|Finish|
          |2|B|2021-01-19 12:00:00|2021-01-21 12:00:00|In Use|

Then, i have code like this:

      $datestart = '2021-01-19 '.Date("H:i");
      $dateend = '2021-01-20 '.Date("H:i");
      $cek =Peminjamanrandis::where('status','!=',"Cancel")
             ->where('status','!=','Selesai')
             ->where('status','!=','Pengajuan Peminjaman')
             ->where('status','!=','Tidak Disetujui')
             ->where(function ($query) use($datestart,$dateend) {
               $query->whereBetween('date_start',[$datestart,$dateend])
                     ->orwhereBetween('date_end',[$datestart,$dateend]);
              })
             ->where(function ($query) use($datestart) {
                 $query->where('date_start', '<=', $datestart)
                       ->orWhere('date_start', '>=',$datestart);
             })
             ->where(function ($query) use($dateend) {
                 $query->where('date_end', '<=', $dateend)
                       ->orWhere('date_end', '>=',$dateend);
             })
          ->get(['id_randis']);

Then I have a form input that received date start and date end. Problem : when users input date start = 2021-01-19 and date end = 2021-01-20 or date start =2021-01-20 and date end= 2021-01-20. It shows nothing.

My goals is when user input like that, it shows vehicle B (ID 2). How can I do that?

0 likes
6 replies
CorvS's avatar

First of all your two last where clauses are always true, so you can omit them.

->where(function ($query) use($datestart) {
    $query->where('date_start', '<=', $datestart) 
        ->orWhere('date_start', '>=',$datestart);
})
 ->where(function ($query) use($dateend) {
    $query->where('date_end', '<=', $dateend)
        ->orWhere('date_end', '>=',$dateend);
})
Dikurr11's avatar

i already try to erase it, but it shows nothing too sir.

CorvS's avatar

If I understand you correctly you want all entries where date_start or date_end is between datestart and dateend?

Peminjamanrandis::whereNotIn('status', ['Cancel', 'Selesai', ...])
    ->whereBetween('date_start', [$datestart, $dateend])
    ->orWhereBetween('date_end', [$datestart, $dateend])
    ->get(['id_randis']);
CorvS's avatar

Try removing .Date('H:i') and check if you are getting results.

Dikurr11's avatar

if date_start 2021-01-19 and date_end 2021-01-20. it shows 1 data. then, if date_start 2021-01-20 and date_end 2021-01-20 . still show nothing sir.

Please or to participate in this conversation.