Laravel where date between

Laravel

Laravel Eloquent has built-in function whereBetween to get the records between two dates but there is problem here. This method does not returns the records which are created on end date.

Let’s say you want to retrieve the created records between two dates then we will use below function,

$data = CustomModel::whereBetween('created_at', ["2021-01-01", "2021-01-31"])->get();

From the code it looks that everything is perfect but there is one issue here. If you observe the records then you will see that records which are created on “2021–01–31” are not fetched because the column “created_at” is having type “timestamp” OR “datetime” which stores full date including hours, minutes & seconds and as we have passed the short date, so Laravel will automatically format the end date as “2021–01–31 00:00:00”.

So to get the data of the end day as well, we need to create our own Scope function.

To create the custom scope functions we will create one our own Trait and then use that trait class in the models. So first let’s create the directory named Traits in the App folder of Laravel. Inside this directory, create one file named Scopes.php and paste below code in that file.

<?php
namespace App\Traits;
trait Scopes
{
    
    public function scopeDateBetween($query, $column, $dates)
    {
        return $query->whereDate($column,">=",$dates[0])->whereDate($column,"<=",$dates[1]);
    }
}

As you can see here we have created new trait class with name Scopes and inside this class we have defined one public method scopeDateBetween. You can use any custom name but make sure you follow below 2 rules,

Function name should start with keyword scope

Function name should be in camel-case

We will discuss about the function parameters on later stage. Now add the below line of code in all the models in which we want to use this scope function.

use \App\Traits\Scopes;

So your final model code will look like this,

<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class CustomModel extends Model
{
    use \App\Traits\Scopes;
    
    protected $table = "custom_model";
    
}

Hurrah, now we are ready to use this function. So let’s update the Eloquent’s query code,

// Laravel's whereBetween method which returns incorrect data
// $data = CustomModel::whereBetween('created_at', ["2021-01-01", "2021-01-31"])->get();
// Our own scope function
$data = CustomModel::dateBetween('created_at', ["2021-01-01", "2021-01-31"])->get();

As you can see, the syntax of custom function is same as Laravel’s whereBetween method. We have passed 2 parameters to scope function. First, the name of column and second, the array of start date & end date.

Now let’s see how this scope function works.

<?php
namespace App\Traits;
trait Scopes
{
    public function scopeDateBetween($query, $column, $dates)
    {
        // $query is Eloquent Object
        // $column will be created_at
        // $dates will be [2021-01-01, 2021-01-31]
    }
}

That’s it for today. Thank you guys and let me know your suggestions in the comments. Keep coding !!