How to Query Between Two Dates Using Laravel and Eloquent

How to query between two dates using Laravel and Eloquent?

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.

Date query between two dates in Laravel Eloquent

Firstly I would recommend using the MySQL TIMESTAMP or DATE field types rather than storing it as a slightly unusual format (d.m.Y). Also consider using Carbon.

Those would allow you to make your life somewhat easier :

$now = Carbon::now(); // Or whatever date you want to use as the start date.
$then = $now->clone()->addDays(10); // Or whatever you want to set the end date as
$infos = Info::whereDate('start_date', '>=', $now)->whereDate('end_date', '<=', $then)->get();

How to filter data between two dates using eloquent laravel?

Try this :

$data = App\Models\Data::where('age_id',$age->id)
            ->where('nationality', $nationality)
            ->when(isset($to), function($q) use($from, $to){
                $q->whereBetween('date', [$from, $to]);
            })
           ->when(!isset($to), function($q) use($from){
                $q->whereDate('date', $from);
            })
          ->get();

Laravel Eloquent: How to use whereDate with Between?

For merging whereDate and between you can use something like this:

User::whereBetween(DB::raw('DATE(created_at)'), array($from_date, $to_date))->get();

sql :

select * from `users` where DATE(created_at) between '2018-02-01' and '2018-02-06'

Laravel find records between two dates

1. You use ->get() on the Eloquent\Builder first.
   then where will not working after get().

2. Because your created_at is timestamp, so I think you need to convert $end_date to the end of day, and the $start_date to the start of day.

Try this code:

$start_date = date('Y-m-d 00:00:00', strtotime($request->get('start_date')));

$end_date = date('Y-m-d 23:59:59', strtotime($request->get('end_date')));

$shipments = Shipments::with(['pick_up_address', 'delivery_address', 'empty_return', 'empty_pick_up']);

if($request->has('start_date')) 
{

       $shipments->where('created_at', '>=', $start_date);
}

if ($request->has('end_date')) 
{
       $shipments->where('created_at', '<=', $end_date);
}

$shipments = $shipments->get();

Retrieve data between two dates in laravel

If you can use Carbon then this code will work fine for you.

$dateS = new Carbon('first day of January 2016');
$dateE = new Carbon('first day of November 2016');
$result = ModelName::whereBetween('created_at', [$dateS->format('Y-m-d')." 00:00:00", $dateE->format('Y-m-d')." 23:59:59"])->get();

Or you can check the issue by debugging query using DB::enableQueryLog(); and DB::getQueryLog(); functions like

DB::enableQueryLog();
$dateS = new Carbon('first day of January 2016');
$dateE = new Carbon('first day of November 2016');
$result = ModelName::whereBetween('created_at', [$dateS->format('Y-m-d')." 00:00:00", $dateE->format('Y-m-d')." 23:59:59"])->get();
var_dump($result, DB::getQueryLog());

Laravel $q->where() between dates

You can chain your wheres directly, without function(q). There's also a nice date handling package in laravel, called Carbon. So you could do something like:

$projects = Project::where('recur_at', '>', Carbon::now())
    ->where('recur_at', '<', Carbon::now()->addWeek())
    ->where('status', '<', 5)
    ->where('recur_cancelled', '=', 0)
    ->get();

Just make sure you require Carbon in composer and you're using Carbon namespace (use Carbon\Carbon;) and it should work.

EDIT:
As Joel said, you could do:

$projects = Project::whereBetween('recur_at', array(Carbon::now(), Carbon::now()->addWeek()))
    ->where('status', '<', 5)
    ->where('recur_cancelled', '=', 0)
    ->get();

---

Related Topics