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:
- DATE: This type stores date values in the format ‘YYYY-MM-DD’.
- TIME: TIME stores time values in the format ‘HH:MM:SS’.
- DATETIME: DATETIME stores both date and time in the format ‘YYYY-MM-DD HH:MM:SS’.
- TIMESTAMP: Similar to DATETIME, TIMESTAMP stores date and time, but it has a different range and automatic update behavior.
- YEAR: YEAR stores a year value in the format ‘YYYY’.
MySQL Date Comparison Operators
Let’s discuss one by one all mysql date operators with examples.
MySQL date EQUALS
The EQUALS (=) operator is used to find records where two date values are equal.
SELECT * FROM employee WHERE joining_date = '2023-10-15';
MySQL date NOT EQUALS (<> or !=)
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';
MySQL date LESS THAN (<)
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';
MySQL date GREATER THAN (>)
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';
LESS THAN OR EQUAL TO (<=) Or GREATER THAN OR EQUAL TO (>=)
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 Date Functions for Comparison
MySQL offers various functions to manipulate and compare date values.
How To Find Date Difference Using DATEDIFF()
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;
How To Add Number of Days in Mysql Date
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;
How To Subtract Day from a date in MySql
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;
How To Format Date in MySQL
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;
How to handle NULL Values in MySQL
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;
WHats is Difference Between CURRENT_TIMESTAMP and NOW()
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.
Conclusion:
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.
