Date and Time Functions

Date and time functions are among the most frequently used in MySQL. They help you extract parts of dates, calculate differences, format timestamps for display, and perform date arithmetic.

Essential Date Functions

NOW(): Current date and time
CURDATE(): Current date only
YEAR(d), MONTH(d), DAY(d): Extract date parts
DATE_FORMAT(d, fmt): Format a date as a string
DATEDIFF(d1, d2): Number of days between two dates
DATE_ADD(d, INTERVAL n unit): Add an interval to a date
TIMESTAMPDIFF(unit, d1, d2): Difference in specified units

Formatting Dates

DATE_FORMAT uses format specifiers like %Y (4-digit year), %m (2-digit month), %d (2-digit day), %H:%i:%s (time). This is essential for generating reports with human-readable dates without post-processing in code.

Example

SELECT
    order_id,
    DATE_FORMAT(created_at, '%d %M %Y') AS order_date,
    DATEDIFF(NOW(), created_at) AS days_old,
    DATE_ADD(created_at, INTERVAL 30 DAY) AS due_date,
    YEAR(created_at) AS order_year
FROM orders
WHERE created_at >= '2025-01-01';

SQL

Full Editor

Pro Tip

Store dates as DATETIME or TIMESTAMP in UTC and use DATE_FORMAT only at display time.

Related Resources

MySQL Reference

Complete tag & property list

MySQL How-To Guides

Step-by-step practical guides

MySQL Exercises

Practice what you've learned