How to Get Data between Two Dates in Laravel

In this blog, we will show you How to Get Data between Two Dates in Laravel. You can use more than examples for fetching records between two dates in laravel using whereBetween(), where(), whereDate() eloquent methods.

Now the Laravel application whereBetween method is more useful when we retrieve records in between two given dates from the database.

Using Laravel whereBetween

First, we use whereBetween() laravel inbuild method to get records between two dates. The below code return posts that are created_at between '2022-07-01' and '2022-07-30'.

<?php
  
namespace App\Http\Controllers;
  
use Illuminate\Http\Request;
use App\Models\Post;
class PostController extends Controller
{
    public function index(Request $request)
    {
        $startDate = '2022-01-01';
        $endDate = '2022-07-30';

        $posts = Post::whereBetween('created_at', [$startDate, $endDate])->get();
        return $posts;
    }
}

Get data between two dates with carbon

For getting data between two dates we can use carbon startOfDay() and endOfDay() methods, These methods work the same as the above example:

<?php
  
namespace App\Http\Controllers;
  
use Illuminate\Http\Request;
use App\Models\Post;
use Carbon\Carbon;
class PostController extends Controller
{
    public function index(Request $request)
    {
        $startDate = Carbon::createFromFormat('Y-m-d', '2022-06-01')->startOfDay();
        $endDate = Carbon::createFromFormat('Y-m-d', '2022-06-30')->endOfDay();

        $posts = Post::whereBetween('created_at', [$startDate, $endDate])->get();
        return $posts;
    }
}

Get data between two dates using Eloquent Query

Eloquent provides a very helpful whereDate() the method that will do two things

1. Build an SQL query that uses the DATE() SQL function to format the content of the column as Y-m-d.
2. Properly cast a Carbon or Datetime object to the Y-m-d format before comparing it.

<?php
  
namespace App\Http\Controllers;
  
use Illuminate\Http\Request;
use App\Models\Post;
use Carbon\Carbon;
class PostController extends Controller
{
    public function index(Request $request)
    {
        $startDate = Carbon::createFromFormat('Y-m-d', '2022-06-01');
        $endDate = Carbon::createFromFormat('Y-m-d', '2022-06-30');
        
        $posts = Post::whereDate('created_at', '>=', $startDate)
            ->whereDate('created_at', '<=', $endDate)
            ->get();
        return $posts;
    }
}

Laravel Date Between Start and End Date

Here the example you can get data between two different-2 columns, here we are getting 'start_at' and 'end_at' columns, you can try this one.

<?php
  
namespace App\Http\Controllers;
  
use Illuminate\Http\Request;
use App\Models\User;
use Carbon\Carbon;
class UserController extends Controller
{
    public function index(Request $request)
    {
        $startDate = Carbon::createFromFormat('Y-m-d', '2022-06-01');
        $endDate = Carbon::createFromFormat('Y-m-d', '2022-06-30');
        $users = User::whereDate('start_at', '>=', $startDate)
                ->whereDate('end_at', '<=', $endDate)
                ->get();

        return $users;
    }
}

Get data between two dates with MySQL Raw Query

We could also explicitly tell MySQL that we only care about the date by using DATE(). The query we want is this:

SELECT * FROM posts WHERE DATE(created_at) BETWEEN '2022-06-01' AND '2022-06-30'

That way we’ll compare dates with dates, and not with a Datetime. We’ll need to resort to DB:raw() to replicate this with Eloquent, which would look like this:

$startDate = '2022-06-01';
$endDate = '2022-06-30';

Post::whereBetween(DB::raw('DATE(created_at)'), [$startDate, $endDate])->get();

Thank you for reading this blog.

Read Also: Eager Loading with Selected Columns in Laravel