Reputation: 13434

How to query between two dates using Laravel and Eloquent?

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?

Upvotes: 254

Answers (15)

tomloprod

Reputation: 7862

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();

Upvotes: 26

ManojKiran

Reputation: 6341

And I have created the model scope

To Know More about scopes check out the below links:

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();

Upvotes: 30

Hadayat Niazi

Reputation: 2470

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.

Upvotes: 2

codelone

Reputation: 752

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();

Upvotes: 1

Amr Abdalrahman Ahmed

Reputation: 1010

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();

Upvotes: 1

Krishnan Unni Suresh

Reputation: 11

@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();

Upvotes: 1

Masoud Malayerian

Reputation: 11

the trick is to change it :

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

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

because the date must be in string type in mysql

Upvotes: 1

Muhammad Ismail

Reputation: 370

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();

Upvotes: 6

ESP-RAY

Reputation: 23

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();

Upvotes: 1

Peter Kota

Reputation: 8340

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.

Upvotes: 482

MIGUEL LOPEZ ARIZA

Reputation: 131

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();

Upvotes: 13

Excellent Lawrence

Reputation: 1104

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;

Upvotes: 4

Nur Uddin

Reputation: 1840

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();

Upvotes: 24

ArtisanBay

Reputation: 1041

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.

Upvotes: 12

Pᴇʜ

Reputation: 57683

The following should work:

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

Upvotes: 18

How to query between two dates using Laravel and Eloquent?

Answers (15)

Related Questions