How to compare date in where query laravel eloquent

Laravel use carbon library to format and show the date and to compare date in where query laravel 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. It parse the date time to date and we can easily compare or match the date.

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.

The Date Format

Laravel Eloquent typically stores dates in the Y-m-d H:i:s format. Understanding this format is pivotal as it forms the basis for date comparisons. Remember, precision matters when dealing with dates, and Laravel expects you to follow suit.

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();

and if we want the same in model

User::whereDate("created_at","2023-11-14")

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();

Date comparisons in Eloquent queries serve as a fundamental tool for scenarios demanding meticulous data filtering within specified temporal boundaries. This capability proves invaluable for tasks such as generating detailed reports, analyzing trends, or extracting data pertinent to particular timeframes.

Pros and Cons:

Pros:

Precision in Querying: Date comparisons allow for pinpoint accuracy in filtering, ensuring the retrieval of precisely targeted data.
Facilitates Time-sensitive Operations: Essential for operations dependent on specific timeframes, such as event scheduling or analytical assessments.

Cons:

Potential for Complexity: In more intricate scenarios, accommodating diverse date formats or time zones may introduce complexities to query construction.
Extended Query Length: Multiple date comparisons can result in longer queries, potentially impacting overall performance.

I hope you like the article and Happy Coding!!

The Date Format

Comparing date using raw query in laravel

Related Posts