This tutorial helps to understand MySQL Date Comparison with examples. MySQL offers a rich set of functions and operators to handle date and time data. We’ll go through working with dates in date comparison.
There are a number of conditions in web applications where we need to to filter, sort, and manipulate data based on date-related conditions.
There are the following date and time data types available in MySQL:
Let’s discuss one by one all mysql date operators with examples.
The EQUALS (=) operator is used to find records where two date values are equal.
SELECT * FROM employee WHERE joining_date = '2023-10-15';
The NOT EQUALS (<> or !=) operator is used to find records where two date values are not equal.
SELECT * FROM employee WHERE joining_date != '2023-10-15';
The LESS THAN (<) operator is used to find records of those joining dates earlier than a specified date.
SELECT * FROM employee WHERE joining_date > '2023-10-15';
The GREATER THAN (>) operator is used to find records of those joining dates later than a specified date.
SELECT * FROM employee WHERE joining_date > '2023-10-15';
We can also use LESS THAN OR EQUAL TO (<=) operator to get records that are earlier than or equal to a specified date.
GREATER THAN OR EQUAL TO (>=) operator to find records which are later than or equal to a specified date.
SELECT * FROM employee WHERE joining_date <= '2023-10-15'; SELECT * FROM employee WHERE joining_date >= '2023-10-15';
MySQL offers various functions to manipulate and compare date values.
The DATEDIFF() function is used to calculate the number of days between two date values.
SELECT DATEDIFF('2023-10-30', '2023-10-15') AS days_between;
The MySQL DATE_ADD() function is used to add a specified number of days to a date.
SELECT DATE_ADD('2023-10-15', INTERVAL 7 DAY) AS new_date;
You can DATE_SUB() method to subtract a specified number of days from a date.
SELECT DATE_SUB('2023-10-15', INTERVAL 3 MONTH) AS new_date;
The MySQL offers DATE_FORMAT() method to format a date as a string according to a specified format.
SELECT DATE_FORMAT('2023-10-15', '%M %d, %Y') AS formatted_date;
You can check if a date column contains NULL values by IS NULL or IS NOT NULL operators to filter records based on date presence.
SELECT * FROM employee WHERE joining_date IS NULL; SELECT * FROM employee WHERE joining_date IS NOT NULL;
The CURRENT_TIMESTAMP is a SQL-standard function that retrieves the current date and time in the format ‘YYYY-MM-DD HH:MM:SS’.
Its have auto-update column features so It will automatically update whenever a new record is inserted or an existing record is updated.
The Now()
is a MySQL-specific function that retrieves the current date and time in the format ‘YYYY-MM-DD HH:MM:SS’. It does not provide the auto-update feature.
The date comparison is a common operation for anyone working with databases containing date and time data. In this article, we’ve covered the essential concepts, operators, and functions for comparing dates in MySQL.
This tutorial helps integrate a PHP SDK with Laravel. We'll install aws-php-sdk into laravel application and access all aws services… Read More
in this quick PHP tutorial, We'll discuss php_eol with examples. PHP_EOL is a predefined constant in PHP and represents an… Read More
This Laravel tutorial helps to understand table Relationships using Elequonte ORM. We'll explore laravel table Relationships usage and best practices… Read More
We'll explore different join methods of Laravel eloquent with examples. The join helps to fetch the data from multiple database… Read More
in this Laravel tutorial, We'll explore valet, which is a development environment for macOS minimalists. It's a lightweight Laravel development… Read More
I'll go through how to use soft delete in Laravel 10 in this post. The soft deletes are a method… Read More