laraveleloquentlaravel-5.1date

Laravel Eloquent Birthdays Query to get Date Column based on Date and Month only

I have a date() column in mysql which has the Date of Birth in mysql’s YYYY-MM-DD format:

$table->date('dob')->nullable();

I am trying to query this table so it lists all users who are having birthdays from today till next 7 days. I have something like this right now:

$date = new Carbon;
$today = $date->format('Y-m-d');
$futureDays = $date->addDays(7)->format('Y-m-d');

$birthdays = SiteUsers::where('dob', '>=', $today)
                        ->where('dob', '<=', $futureDays)
                        ->orderBy('dob', 'ASC')
                        ->get();

The problem with the above query is that it only lists the users having DOB with the exact dates in this year only which is not right. What I really want is to list the users having birthdays irrespective of the year they were born by matching only the ‘date’ and ‘month’ and ignoring the ‘year’.

I don’t know how to achieve that. Can someone help me out please…

Solution

  • Shortly after I posted my question, I tried a method using raw queries that uses DateOfYear() SQL Function. So this is what I have now which seems to be working by getting the users with birthdays in the next 7 days irrespective of the year:

    $birthdays = SiteUsers::whereRaw('DAYOFYEAR(curdate()) <= DAYOFYEAR(dob) AND DAYOFYEAR(curdate()) + 7 >=  dayofyear(dob)')
                        ->orderByRaw('DAYOFYEAR(dob)')
                        ->get();

    enjoy