Filter by month with format 'Y-m-d'

Question

i have a date with format Y-m-d. How can i filter it by month? I specified the month by numbers like January = 1, February = 2 and so and so forth.

public function scopegetmonthleavereport($query,$payroll_employee_id,$month)
{

         $query->join('tbl_payroll_leave_employee_v2','tbl_payroll_leave_schedulev2.payroll_leave_employee_id','=','tbl_payroll_leave_employee_v2.payroll_leave_employee_id')
         ->join("tbl_payroll_employee_basic","tbl_payroll_leave_employee_v2.payroll_employee_id","=","tbl_payroll_employee_basic.payroll_employee_id")
         ->join("tbl_payroll_leave_tempv2","tbl_payroll_leave_employee_v2.payroll_leave_temp_id","=","tbl_payroll_leave_tempv2.payroll_leave_temp_id")
         ->select(DB::raw('tbl_payroll_employee_basic.payroll_employee_id , tbl_payroll_leave_schedulev2.payroll_schedule_leave, tbl_payroll_employee_basic.payroll_employee_display_name, tbl_payroll_leave_schedulev2.payroll_leave_temp_with_pay, tbl_payroll_leave_tempv2.payroll_leave_type_id, tbl_payroll_leave_employee_v2.payroll_leave_employee_id, tbl_payroll_leave_schedulev2.consume, tbl_payroll_leave_employee_v2.payroll_leave_temp_hours, sum(tbl_payroll_leave_schedulev2.consume) as total_leave_consume, (tbl_payroll_leave_employee_v2.payroll_leave_temp_hours - sum(tbl_payroll_leave_schedulev2.consume)) as remaining_leave'))
         ->groupBy('tbl_payroll_leave_employee_v2.payroll_leave_temp_id')
         ->where('tbl_payroll_leave_schedulev2.payroll_leave_schedule_archived',0)
         ->where('tbl_payroll_leave_employee_v2.payroll_employee_id', $payroll_employee_id)
         ->whereBetween('tbl_payroll_leave_schedulev2.payroll_schedule_leave', $date);  //where i want to put the date


    return $query;
}

god query builders are so `ugly` but in SQL you do `WHERE MONTH(date_field) = :month` Reminds me I have to build my ORM with SQL lexer/parser... — ArtisticPhoenix, Nov 23 '17 at 05:34
@ArtisticPhoenix how can i do it with eloquent in laravel bro? haha — hihihihi, Nov 23 '17 at 05:41
I don't know I don't use Laravel, but that query builder looks like `crap` to me ( from a readability standpoint ),. I been writing SQL for 7 years, — ArtisticPhoenix, Nov 23 '17 at 05:43
`whereMonth` I did it using this line haha. Thank you for the clue bro — hihihihi, Nov 23 '17 at 05:43
Sure, I wish I could have told you directly. But i work with the basics, always seem cleaner that way. I mean I get ORMs, I've `regretfully` used them ( Doctrine ) and I find that they wind up being like a pair of handcuffs for me. — ArtisticPhoenix, Nov 23 '17 at 05:45
https://stackoverflow.com/questions/851236/where-clause-to-find-all-records-in-a-specific-month credits to this thread tho :) — hihihihi, Nov 23 '17 at 05:46
Possible duplicate of [WHERE Clause to find all records in a specific month](https://stackoverflow.com/questions/851236/where-clause-to-find-all-records-in-a-specific-month) — hihihihi, Nov 23 '17 at 08:59

score 1 · Accepted Answer · answered Nov 23 '17 at 08:58

1

Try like and regex Inside where, use something like this:

->Where('date', 'like', '%-' .$month. '-%')->get();

I have not tried it.

answered Nov 23 '17 at 08:58

Vishnu M Raveendran

391
4
12

score 0 · Answer 2 · answered Nov 23 '17 at 05:41

use Carbon\Carbon;

public function index(){
        date_default_timezone_set('Asia/Kolkata');

        //Total Students
         $total_students = User::where('role_id','2')->count();         

        $today = Carbon::today()->toDateTimeString();
         //dd($today);
          $first_day = date('Y, n, j', strtotime('-1 day', strtotime($today)));
            $second_day = date('Y, n, j', strtotime('-2 day', strtotime($today)));
            $third_day = date('Y, n, j', strtotime('-3 day', strtotime($today)));
            $fourth_day = date('Y, n, j', strtotime('-4 day', strtotime($today)));
            $fifth_day = date('Y, n, j', strtotime('-5 day', strtotime($today)));
            $sixth_day = date('Y, n, j', strtotime('-6 day', strtotime($today)));
            $seventh_day = date('Y, n, j', strtotime('-7 day', strtotime($today)));     

        return view('home',compact('total_students','total_doner','current_day_student','first_day','second_day','third_day','fourth_day','fifth_day'
                ,'sixth_day','seventh_day'));
    }

this example will help you – Gowthaman D Nov 23 '17 at 05:41 — Gowthaman D, Nov 23 '17 at 05:41

Filter by month with format 'Y-m-d'

2 Answers2