skip to Main Content

I’m truing to get the employees that have birthday within next 3 days. I tried to do that by using the following query.

public function upcomingBirthdays()
{
    $from = now()->format('m-d');
    $to = now()->addDays(3)->format('m-d');

    $employees = Employees::whereRaw("DATE_FORMAT(dob, '%m-%d') BETWEEN '{$from}' AND '{$to}'")
                          ->where('team_id', 13)
                          ->where('status', 1)
                          ->orderBy('dob', 'DESC')
                          ->get();

    return view('frontend.employee.birthdays', compact('employees'));
}
// End Method

But this is not returning expected data.

Today is 2022-11-09

This is returning employees with birthdays between 2022-11-08 and 2022-11-10. But not returning employees that have birthdays on 11-11 and 11-12.

3

Answers


  1. Chosen as BEST ANSWER

    The solution that finally worked for me:

    $employees = Employees::where('team_id', 13)
                            ->where('status', 1)
                            ->where(function($q){
                                $q->where('dob', 'like', '%' . Carbon::now()->format('m-d'))
                                ->orWhere('dob', 'like', '%' . Carbon::now()->addDays()->format('m-d'))
                                ->orWhere('dob', 'like', '%' . Carbon::now()->addDays(2)->format('m-d'))
                                ->orWhere('dob', 'like', '%' . Carbon::now()->addDays(3)->format('m-d'))
                                ->orderBy(DB::raw("DATE_FORMAT(dob,'%m-%d')"), 'ASC');
                            })
                            ->get();
    

  2. The problem is probably related with the date formatting. Using DD instead of d you get a leading zero for single digit day of the month. Same for month: use MM instead of m.

    Note: This will still fail for birthdays falling on January 1, 2 and 3.

    Login or Signup to reply.
  3. You probably need a Carbon package from nesbot to handling date.
    I haven’t tested querying within date with built-in PHP but my code works with Carbon package.

    So, on your case, try this:

    $employees = Employee::whereBetween('dob', [Carbon::now(), Carbon::now()->addDay(3)])->get();
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search