CODEWITHSUNDEEP
laravel-5eloquent
Arunwij
Arunwij

Reputation: 400

Retrieve data between two dates in laravel

I am using an eloquent query to retrieve data from a table. The table columns look like this:

id   started_at   finished_at   
1    2016-06-01   2016-06-30  
2    2016-05-03   2016-05-28

What I want to do is, given a $date (ex: 2016-06-18 ) and get the data of the row, that the $date between started at and finished_at columns.

I have found whereBetween clauses in Laravel documentation, but I do not have an idea to use it correctly.

Upvotes: 19

Views: 51800

Answers (5)

Abdul Moiz
Abdul Moiz

Reputation: 492

$startDate = '2024-5-17';

$endDate = '2024-5-17';

if the column is timestamp or datetime then it will generate a query like

User::whereBetween('created_at',[$startDate, $endDate])->get();
// SELECT * FROM users where created_at between 2024-5-17 00:00:00 and 2024-5-17 00:00:00

if you use the date(created_at) MySQL function then it will only focus on the date, not the time

use Illuminate\Support\Facades\DB;
User::whereBetween(DB::raw('DATE(created_at)'),[$startDate, $endDate])->get();
// SELECT * FROM users where created_at between 2024-5-17 and 2024-5-17

or you can do

User::whereBetween('created_at',[$startDate, $endDate.' 23:59:59'])->get();
// SELECT * FROM users where created_at between 2024-5-17 00:00:00 and 2024-5-17 23:59:59

Upvotes: 0

MD TAREK HOSSEN
MD TAREK HOSSEN

Reputation: 419

Import carbon class in your controller

use Carbon\Carbon;

For date time column:

ModelName::whereBetween('column_name',[Carbon::parse('2016-06-01')->format('Y-m-d 00:00:00'),Carbon::parse('2016-06-30')->format('Y-m-d 23:59:59')])->get();

For date column

ModelName::whereBetween('column_name',[Carbon::parse('2016-06-01')->format('Y-m-d'),Carbon::parse('2016-06-30')->format('Y-m-d')])->get();

Note: date may come from user input so carbon will format the date.

Upvotes: 0

Pawan Verma
Pawan Verma

Reputation: 1269

You can use whereBetween something like this:-

ModelName::whereBetween('date',['2022-08-01','2022-10-30'])->get();

Upvotes: 0

Lucas Mezêncio
Lucas Mezêncio

Reputation: 181

Try to do something like this:

$date1 = Carbon::today()->toDateString();
$date2 = Carbon::today()->toDateString();

$myModel = MyModel::find(1);
$myModel->whereBetween('created_at', [$date1, $date2]);
$myModel->get();

Of course, you will need to change the dates.

Upvotes: 13

Brn.Rajoriya
Brn.Rajoriya

Reputation: 1544

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

Upvotes: 36

Related Questions