CODEWITHSUNDEEP
phpmysqllaravel
Dev
Dev

Reputation: 6720

Laravel get records resides in month and year

I am using Laravel 5.2.

I have a database table schedules as: 

id    name     start_date    end_date
1     AAAAA    2016-01-01    2016-12-31
2     BBBBB    2016-02-01    2016-03-31
3     CCCCC    2016-04-01    2016-06-12
4     DDDDD    2016-04-18    2016-07-15
5     EEEEE    2016-03-15    2016-04-14
6     FFFFF    2016-05-01    2016-08-26

Now, I want to fetch the records of schedules that are resides in current month i.e. April-2016. The result should be 

id    name     start_date    end_date
1     AAAAA    2016-01-01    2016-12-31
3     CCCCC    2016-04-01    2016-06-12
4     DDDDD    2016-04-18    2016-07-15
5     EEEEE    2016-03-15    2016-04-14

I tried using, 

Schedule::whereDate('start_date', '<=', '2016-04-01')
->whereDate('end_date', '>=', '2016-04-30')
->get();

But it excludes records with id 1 and 4.

How can I achieve this?

Upvotes: 1

Views: 1868

Answers (2)

mcklayin
mcklayin

Reputation: 1360

This code pass your criterias. Try it.

DB::select(DB::raw("SELECT * FROM schedules WHERE start_date <= '2016-04-30' AND end_date >= '2016-04-01'"))

Or with Eloquent:

Schedule::where('start_date','<=', '2016-04-30')->where('end_date','>=', '2016-04-01')->get();

Upvotes: 2

Mysteryos
Mysteryos

Reputation: 5791

Check for both inclusive & overlapping date ranges:

Schedule::where(function($q) use($start_date,$end_date) {
    //Inclusive Date Range
    return $q->orWhere(function($q) use($start_date,$end_date) {
        return $q->where('start_date','>=',$start_date)
            ->where('end_date','<=',$end_date);
    })
    //Overlapping Date Range
    ->orWhere(function($q) use($start_date,$end_date) {
        return $q->where('start_date','<=',$start_date)
            ->where('end_date','>=',$end_date);
    });
})
->get();

Upvotes: 1

Related Questions