Compare date in where query in laravel eloquent

Laravel use carbon library to format and show the date and in laravel eloquent mode or db builder it uses carbon internally so compare date in where query in laravel is almost same as executing where using other data type but if we wanted to go more specific then we can use whereDate method of laravel eloquent.

In this article we will learn to use whereDate, whereYear, whereMonth, whereDay and whereTime function in laravel eloquent and query builder. To compare with MySql datetime we can use thease above function.

These methods works same as where method like we can pass multiple parameters to it. First parameter can be column or closure function and second can be operator or value ans so on.

Here is the syntax

whereDate('COLUMN_NAME',date_value);

//OR

whereYear('COLUMN_NAME',date_value);

//OR

whereMonth('COLUMN_NAME',date_value);
//OR

whereDay('COLUMN_NAME',date_value);
//OR

whereTime('COLUMN_NAME',date_value);

In the above syntax we have used whereDate, whereYear, whereMonth, whereDay and whereTime function to compare the date in laravel .

Example:

$users = DB::table('users')
                ->whereDate('created_at', '2016-12-31')
                ->get();

//or

$users = DB::table('users')
                ->whereYear('created_at', '2021')
                ->get();
//or

$users = DB::table('users')
                ->whereMonth('created_at', '12')
                ->get();
//or

$users = DB::table('users')
                ->whereDay('created_at', '1')
                ->get();

Comparing date using raw query in laravel

We can also compare the date time in laravel using raw query or simple where condition. we will use DB::raw method to compare this.

$users = DB::table('users')
                ->where('created_at', '2016-12-31')
                ->get();

//or

$users = DB::table('users')
                ->where(\DB::raw('date("created_at")', '2021')
                ->get();
 

Comparing date in using operators like greater then equal to etc.

$users = DB::table('users')
                ->whereDate('created_at',">=", '2016-12-31')
                ->get();