How to Get Data between Two Dates in Laravel

by

Laravel get Data between Two Dates example; In this tutorial we will show you How to get data between two dates in Laravel using Eloquent query. You can use more then examples for fetching records between two dates in laravel using whereBetween(), where(), whereDate() eloquent methods.

We are getting data between two dates like created_at, start and end date, paid at etc. We have used laravel whereBetween(), where() and whereDate() eloquent function to get data between two dates.

Using Laravel whereBetween

First we are use whereBetween laravel inbuild method to get records between two dates. The below code return posts which are created_at between ‘ 2021-01-01’ and ‘ 2021-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 = '2021-01-01';
        $endDate = '2021-07-30';

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

Get data betweeen two dates with carbon

For getting data between two dates we can use carbon startOfDay() and endOfDay() methods, These methods work same as 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', '2021-06-01')->startOfDay();
        $endDate = Carbon::createFromFormat('Y-m-d', '2021-06-30')->endOfDay();

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

Get data betweeen two dates using Eloquent Query

Eloquent provides a very helpful whereDate() 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', '2021-06-01');
        $endDate = Carbon::createFromFormat('Y-m-d', '2021-06-30');
        
        $posts = Post::whereDate('created_at', '>=', $startDate)
            ->whereDate('created_at', '<=', $endDate)
            ->get();
        dd($posts);
    }
}

This will generate this SQL query :

SELECT * from "posts"
WHERE DATE("created_at") >= '2021-06-01'
AND DATE("created_at") <= '2021-06-30';

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', '2021-06-01');
        $endDate = Carbon::createFromFormat('Y-m-d', '2021-06-30');
        $users = User::whereDate('start_at', '>=', $startDate)
                ->whereDate('end_at', '<=', $endDate)
                ->get();

        dd($users);        
    }
}

Get data betweeen 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 '2021-06-01' AND '2021-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 = '2021-06-01';
$endDate = '2021-06-30';

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

So, frinds today you learned more then examples to get data between two dates in laravel application. I hope these examples help you.

Leave a Comment