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

1. CURDATE() – Get Current Date

CURDATE() returns the current date without any time component. SELECT CURDATE(); Typical result:

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

2. NOW() – Get Current Date and Time

NOW() returns the current date and time. SELECT NOW(); Typical result:

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

3. DATE_FORMAT() – Format a Date

DATE_FORMAT() converts a date or datetime value to a string using a specified format.

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

Typical result:

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

4. DATEDIFF() – Calculate Date Difference

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

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

Typical result:

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

5. DATE_ADD() – Add an Interval to a Date

DATE_ADD() adds a specified time interval to a date or datetime value.

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

Typical result:

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

6. DATE_SUB() – Subtract an Interval from a Date

DATE_SUB() subtracts a specified time interval from a date or datetime value.

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

Typical result:

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

7. DATE() – Extract Date Part

DATE() extracts the date portion from a datetime expression. SELECT DATE(NOW()) AS extracted_date; Typical result:

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

Conclusion

By mastering these MySQL date functions, you can manipulate and query date and time data more flexibly, improving the efficiency and accuracy of your database operations in real‑world projects.