Master MySQL Date Functions: CURDATE, NOW, DATE_FORMAT, DATEDIFF, and More

MySQL provides a rich set of date and time functions for handling temporal data. This article introduces the most commonly used functions with syntax and example output.

CURDATE() – Get Current Date

The CURDATE() function returns the current date without time.

SELECT CURDATE();

+------------+
| CURDATE()  |
+------------+
| 2024-01-18 |
+------------+

NOW() – Get Current Date and Time

The NOW() function returns the current date and time.

SELECT NOW();

+---------------------+
| NOW()               |
+---------------------+
| 2024-01-18 13:30:45 |
+---------------------+

DATE_FORMAT() – Format Date

The DATE_FORMAT() function formats a date value according to a format string.

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') AS formatted_date;

+---------------------+
| formatted_date      |
+---------------------+
| 2024-01-18 13:30:45 |
+---------------------+

DATEDIFF() – Calculate Date Difference

The DATEDIFF() function returns the number of days between two dates.

SELECT DATEDIFF('2024-01-20', '2024-01-18') AS date_difference;

+------------------+
| date_difference |
+------------------+
| 2                |
+------------------+

DATE_ADD() – Add Interval to Date

The DATE_ADD() function adds a time interval to a date.

SELECT DATE_ADD(NOW(), INTERVAL 7 DAY) AS future_date;

+---------------------+
| future_date         |
+---------------------+
| 2024-01-25 13:30:45 |
+---------------------+

DATE_SUB() – Subtract Interval from Date

The DATE_SUB() function subtracts a time interval from a date.

SELECT DATE_SUB(NOW(), INTERVAL 3 MONTH) AS past_date;

+---------------------+
| past_date           |
+---------------------+
| 2023-10-18 13:30:45 |
+---------------------+

DATE() – Extract Date Part

The DATE() function extracts the date part from a datetime value.

SELECT DATE(NOW()) AS extracted_date;

+---------------------+
| extracted_date      |
+---------------------+
| 2024-01-18          |
+---------------------+

By using these functions appropriately, developers can simplify date handling in MySQL, improve query readability, and increase data processing efficiency.