How to query between two dates using Laravel and Eloquent?

Question

I'm trying to create a report page that shows reports from a specific date to a specific date. Here's my current code:

$now = date('Y-m-d');
$reservations = Reservation::where('reservation_from', $now)->get();

What this does in plain SQL is select * from table where reservation_from = $now.

I have this query here but I don't know how to convert it to eloquent query.

SELECT * FROM table WHERE reservation_from BETWEEN '$from' AND '$to

How can I convert the code above to eloquent query?

Answer 1

The whereBetween method verifies that a column's value is between two values.

$from = date('2018-01-01');
$to = date('2018-05-02');

Reservation::whereBetween('reservation_from', [$from, $to])->get();

---

In some cases you need to add date range dynamically. Based on @Anovative's comment you can do this:

Reservation::all()->filter(function($item) {
  if (Carbon::now()->between($item->from, $item->to)) {
    return $item;
  }
});

---

If you would like to add more condition then you can use orWhereBetween. If you would like to exclude a date interval then you can use whereNotBetween .

Reservation::whereBetween('reservation_from', [$from1, $to1])
  ->orWhereBetween('reservation_to', [$from2, $to2])
  ->whereNotBetween('reservation_to', [$from3, $to3])
  ->get();

---

Other useful where clauses: whereIn, whereNotIn, whereNull, whereNotNull, whereDate, whereMonth, whereDay, whereYear, whereTime, whereColumn , whereExists, whereRaw.

Laravel docs about Where Clauses.

Answer 2

And I have created the model scope

To Know More about scopes check out the below links:

• laravel.com/docs/eloquent#query-scopes

• medium.com/@janaksan/using-scope-with-laravel

Code:

   /**
     * Scope a query to only include the last n days records
     *
     * @param  \Illuminate\Database\Eloquent\Builder $query
     * @return \Illuminate\Database\Eloquent\Builder
     */
    public function scopeWhereDateBetween($query,$fieldName,$fromDate,$todate)
    {
        return $query->whereDate($fieldName,'>=',$fromDate)->whereDate($fieldName,'<=',$todate);
    }

And in the controller, add the Carbon Library to top

use Carbon\Carbon;

To get the last 10 days record from now

 $lastTenDaysRecord = ModelName::whereDateBetween('created_at',(new Carbon)->subDays(10)->startOfDay()->toDateString(),(new Carbon)->now()->endOfDay()->toDateString() )->get();

To get the last 30 days record from now

 $lastThirtyDaysRecord = ModelName::whereDateBetween('created_at',(new Carbon)->subDays(30)->startOfDay()->toDateString(),(new Carbon)->now()->endOfDay()->toDateString() )->get();

Answer 3

Another option if your field is datetime instead of date (although it works for both cases):

$fromDate = "2016-10-01";
$toDate = "2016-10-31";

$reservations = Reservation::whereRaw(
  "(reservation_from >= ? AND reservation_from <= ?)", 
  [
     $fromDate ." 00:00:00", 
     $toDate ." 23:59:59"
  ]
)->get();

Answer 4

If you want to check if current date exist in between two dates in db: =>here the query will get the application list if employe's application from and to date is exist in todays date.

$list=  (new LeaveApplication())
            ->whereDate('from','<=', $today)
            ->whereDate('to','>=', $today)
            ->get();

Answer 5

The following should work:

$now = date('Y-m-d');
$reservations = Reservation::where('reservation_from', '>=', $now)
                           ->where('reservation_from', '<=', $to)
                           ->get();

Answer 6

If you need to have in when a datetime field should be like this.

return $this->getModel()->whereBetween('created_at', [$dateStart." 00:00:00",$dateEnd." 23:59:59"])->get();

Answer 7

Try this:

Since you are fetching based on a single column value you can simplify your query likewise:

$reservations = Reservation::whereBetween('reservation_from', array($from, $to))->get();

Retrieve based on condition: laravel docs

Hope this helped.

Answer 8

I followed the valuable solutions provided by other contributors and faced a minor issue no one has addressed. If the reservation_from is a datetime column then it might not produce results as expected and will miss all the records in which the date is same but time is anything above 00:00:00 time. To improve the above code a bit a small tweak is needed like so.

$from = Carbon::parse();
$to = Carbon::parse();
$from = Carbon::parse('2018-01-01')->toDateTimeString();
//Include all the results that fall in $to date as well
$to = Carbon::parse('2018-05-02')
    ->addHours(23)
    ->addMinutes(59)
    ->addSeconds(59)
    ->toDateTimeString();
//Or $to can also be like so
$to = Carbon::parse('2018-05-02')
    ->addHours(24)
    ->toDateTimeString();
Reservation::whereBetween('reservation_from', [$from, $to])->get();

Answer 9

I know this might be an old question but I just found myself in a situation where I had to implement this feature in a Laravel 5.7 app. Below is what worked from me.

 $articles = Articles::where("created_at",">", Carbon::now()->subMonths(3))->get();

You will also need to use Carbon

use Carbon\Carbon;

Answer 10

Let me add proper syntax with the exact timestamp

Before

$from = $request->from;
$to = $request->to;
Reservation::whereBetween('reservation_from', [$from, $to])->get();

After

$from = date('Y-m-d', strtotime($request->from));
$to = date('Y-m-d', strtotime($request->to));
Reservation::whereBetween('reservation_from', [$from, $to])->get();

Note: if you stored date in string form, then make sure to pass the exact format in from and to. Which will be matched with DB.

Answer 11

Here's my answer is working thank you Artisan Bay i read your comment to use wheredate()

It Worked

public function filterwallet($id,$start_date,$end_date){

$fetch = DB::table('tbl_wallet_transactions')
->whereDate('date_transaction', '>=', $start_date)                                 
->whereDate('date_transaction', '<=', $end_date)                                 
->get();

Answer 12

the trick is to change it :

Reservation::whereBetween('reservation_from', [$from, $to])->get();

to

Reservation::whereBetween('reservation_from', ["$from", "$to"])->get();

because the date must be in string type in mysql

Answer 13

@masoud , in Laravel, you have to request the field value from form.So,

    Reservation::whereBetween('reservation_from',[$request->from,$request->to])->get();

And in livewire, a slight change-

    Reservation::whereBetween('reservation_from',[$this->from,$this->to])->get();

Answer 14

you can use DB::raw('') to make the column as date MySQL with using whereBetween function as :

    Reservation::whereBetween(DB::raw('DATE(`reservation_from`)'),
    [$request->from,$request->to])->get();

Answer 15

Another way:

use Illuminate\Support\Facades\DB;

$trans_from = date('2022-10-08');
$trans_to = date('2022-10-12');
$filter_transactions =  DB::table('table_name_here')->whereBetween('created_at', [$trans_from, $trans_to])->get();