CODEWITHSUNDEEP
phpmysqllaraveleloquent
Khirad2019
Khirad2019

Reputation: 35

Search Start & End dates between start and end date columns MySQL,Laravel

This is my events Table structure.

+----+----------------------+---------------------+
| id |      start_date      |      end_date       |
+----+----------------------+---------------------+
|  1 | 2020-07-01 09:00:00  | 2021-07-11 15:00:00 |
+----+----------------------+---------------------+

I want to fetch all records in a given time period e.g I want to fetch all records that are between 2020-07-27 - 2020-09-07. This is what i have tried so far

$start_date = Carbon::parse("$req->startDate 00:00:00")->format('Y-m-d H:i:s');
$end_date= Carbon::parse("$req->endDate 23:59:59")->format('Y-m-d H:i:s');


Events::whereBetween('start_date', [$start_date , $end_date])->orWhereBetween('end_date', [$start_date , $end_date])->get();

It doesn't fetches any records. Can you guys please check it and guide me what i am missing or doing wrong?

Upvotes: 3

Views: 3734

Answers (3)

OMR
OMR

Reputation: 12208

first of all, you don't need to format the date to use it in 'between'.... just use it as it is ....

second: for getting the first hour in day or the last you can use: startOfDay(),endOfDay() methods:

$start_date = Carbon::parse($req->startDate )->startOfDay();
$end_date= Carbon::parse($req->endDate)->endOfDay();
Events::whereBetween('start_date', [$start_date , $end_date])->orWhereBetween('end_date', [$start_date , $end_date])->get();

Upvotes: 1

Dilip Hirapara
Dilip Hirapara

Reputation: 15296

Try the query as below.

$start_date = Carbon::parse("$req->startDate 00:00:00")->format('Y-m-d H:i:s');
$end_date = Carbon::parse("$req->endDate 23:59:59")->format('Y-m-d H:i:s');

$events = Events::where([['start_date','<=',$start_date],['end_date','>=',$end_date]])
              ->orwhereBetween('start_date',array($start_date,$end_date))
             ->orWhereBetween('end_date',array($start_date,$end_date))->get();

Upvotes: 5

Irshad Khan
Irshad Khan

Reputation: 432

Try this coe,

$start_date = Carbon::parse("$req->startDate 00:00:00")->format('Y-m-d H:i:s');
$end_date= Carbon::parse("$req->endDate 23:59:59")->format('Y-m-d H:i:s');


Events::whereBetween('start_date', [$start_date->toDateTimeString() , $end_date- 
>toDateTimeString()])->orWhereBetween('end_date', [$start_date->toDateTimeString() , 
$end_date->toDateTimeString()])->get();

Upvotes: 0

Related Questions