This tutorial helps to know MySQL Date Comparability with examples. MySQL gives a wealthy set of capabilities and operators to deal with date and time knowledge. We’ll undergo working with dates in date comparability.
There are a selection of circumstances in net functions the place we have to to filter, type, and manipulate knowledge primarily based on date-related circumstances.
There are the next date and time knowledge sorts obtainable in MySQL:
- DATE: This kind shops date values within the format ‘YYYY-MM-DD’.
- TIME: TIME shops time values within the format ‘HH:MM:SS’.
- DATETIME: DATETIME shops each date and time within the format ‘YYYY-MM-DD HH:MM:SS’.
- TIMESTAMP: Much like DATETIME, TIMESTAMP shops date and time, however it has a distinct vary and automated replace conduct.
- YEAR: YEAR shops a yr worth within the format ‘YYYY’.
MySQL Date Comparability Operators
Let’s talk about one after the other all mysql date operators with examples.
MySQL date EQUALS
The EQUALS (=) operator is used to seek out data the place two date values are equal.
SELECT * FROM worker WHERE joining_date = '2023-10-15';
MySQL date NOT EQUALS (<> or !=)
The NOT EQUALS (<> or !=) operator is used to seek out data the place two date values usually are not equal.
SELECT * FROM worker WHERE joining_date != '2023-10-15';
MySQL date LESS THAN (<)
The LESS THAN (<) operator is used to seek out data of these becoming a member of dates sooner than a specified date.
SELECT * FROM worker WHERE joining_date > '2023-10-15';
MySQL date GREATER THAN (>)
The GREATER THAN (>) operator is used to seek out data of these becoming a member of dates later than a specified date.
SELECT * FROM worker WHERE joining_date > '2023-10-15';
LESS THAN OR EQUAL TO (<=) Or GREATER THAN OR EQUAL TO (>=)
We will additionally use LESS THAN OR EQUAL TO (<=) operator to get data which might be sooner than or equal to a specified date.
GREATER THAN OR EQUAL TO (>=) operator to seek out data that are later than or equal to a specified date.
SELECT * FROM worker WHERE joining_date <= '2023-10-15'; SELECT * FROM worker WHERE joining_date >= '2023-10-15';
MySQL Date Capabilities for Comparability
MySQL gives numerous capabilities to govern and examine date values.
How To Discover Date Distinction Utilizing DATEDIFF()
The DATEDIFF() operate is used to calculate the variety of days between two date values.
SELECT DATEDIFF('2023-10-30', '2023-10-15') AS days_between;
How To Add Variety of Days in Mysql Date
The MySQL DATE_ADD() operate is used so as to add a specified variety 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’ll be able to DATE_SUB() technique to subtract a specified variety 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 gives DATE_FORMAT() technique to format a date as a string in response to a specified format.
SELECT DATE_FORMAT('2023-10-15', '%M %d, %Y') AS formatted_date;
Methods to deal with NULL Values in MySQL
You’ll be able to test if a date column accommodates NULL values by IS NULL or IS NOT NULL operators to filter data primarily based on date presence.
SELECT * FROM worker WHERE joining_date IS NULL; SELECT * FROM worker WHERE joining_date IS NOT NULL;
WHats is Distinction Between CURRENT_TIMESTAMP and NOW()
The CURRENT_TIMESTAMP is a SQL-standard operate that retrieves the present date and time within the format ‘YYYY-MM-DD HH:MM:SS’.
Its have auto-update column options so It would routinely replace every time a brand new document is inserted or an present document is up to date.
The Now() is a MySQL-specific operate that retrieves the present date and time within the format ‘YYYY-MM-DD HH:MM:SS’. It doesn’t present the auto-update function.
Conclusion:
The date comparability is a typical operation for anybody working with databases containing date and time knowledge. On this article, we’ve coated the important ideas, operators, and capabilities for evaluating dates in MySQL.
