Date & Time Functions

Introduction

Dates and times show up everywhere in real data — birthdates, order dates, sign-up dates, appointment times. MySQL provides dedicated functions to read the current date and time, format dates for display, measure the gap between two dates, and shift a date forward or backward.

In this lesson we will use a "students" table with a birthdate column, and an "orders" table with an order_date column, to explore NOW(), CURDATE(), CURTIME(), DATE_FORMAT(), DATEDIFF(), DATE_ADD(), and DATE_SUB().

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    birthdate DATE
);

INSERT INTO students (id, name, birthdate) VALUES
(1, 'Alex', '2008-03-15'),
(2, 'Priya', '2007-11-02');

CREATE TABLE orders (
    id INT PRIMARY KEY,
    student_id INT,
    order_date DATE
);

INSERT INTO orders (id, student_id, order_date) VALUES
(1, 1, '2026-07-10'),
(2, 2, '2026-06-28');

Why Date Functions Matter

Storing a raw date like 2008-03-15 is useful for the database, but applications usually need to display it more readably, calculate an age from it, or find out how long ago something happened. Date functions let you do all of this directly inside SQL.

NOW(), CURDATE(), CURTIME()

NOW() returns the current date and time together. CURDATE() returns only the current date. CURTIME() returns only the current time.

SELECT NOW() AS current_datetime, CURDATE() AS current_date, CURTIME() AS current_time;

+---------------------+---------------+---------------+
| current_datetime    | current_date  | current_time  |
+---------------------+---------------+---------------+
| 2026-07-26 09:15:42 | 2026-07-26    | 09:15:42      |
+---------------------+---------------+---------------+

DATE_FORMAT()

DATE_FORMAT() converts a date into a custom text format using format specifiers, which is ideal for displaying dates in a friendlier way than the default YYYY-MM-DD.

SELECT name, birthdate, DATE_FORMAT(birthdate, '%W, %M %e, %Y') AS formatted_birthdate
FROM students;

+-------+------------+-------------------------------+
| name  | birthdate  | formatted_birthdate           |
+-------+------------+-------------------------------+
| Alex  | 2008-03-15 | Saturday, March 15, 2008      |
| Priya | 2007-11-02 | Friday, November 2, 2007      |
+-------+------------+-------------------------------+

DATEDIFF()

DATEDIFF() returns the number of days between two dates. It subtracts the second date from the first, so the order of arguments determines whether the result is positive or negative.

SELECT order_date, DATEDIFF(CURDATE(), order_date) AS days_since_order
FROM orders;

+------------+-------------------+
| order_date | days_since_order  |
+------------+-------------------+
| 2026-07-10 | 16                |
| 2026-06-28 | 28                |
+------------+-------------------+

DATE_ADD() and DATE_SUB()

DATE_ADD() adds a given interval of time to a date, and DATE_SUB() subtracts a given interval of time from a date. Both accept an INTERVAL expression such as INTERVAL 7 DAY or INTERVAL 1 MONTH.

SELECT
    order_date,
    DATE_ADD(order_date, INTERVAL 7 DAY) AS expected_delivery,
    DATE_SUB(order_date, INTERVAL 1 MONTH) AS one_month_before
FROM orders;

+------------+--------------------+-------------------+
| order_date | expected_delivery  | one_month_before  |
+------------+--------------------+-------------------+
| 2026-07-10 | 2026-07-17         | 2026-06-10        |
| 2026-06-28 | 2026-07-05         | 2026-05-28        |
+------------+--------------------+-------------------+

Reference Table

Here is a quick reference for the date and time functions covered in this lesson.

Worked Example: Age and Days Since Order

Let's calculate each student's current age from their birthdate, and how many days ago their most recent order was placed.

SELECT
    s.name,
    DATE_FORMAT(s.birthdate, '%M %e, %Y') AS formatted_birthdate,
    FLOOR(DATEDIFF(CURDATE(), s.birthdate) / 365) AS age,
    DATEDIFF(CURDATE(), o.order_date) AS days_since_order
FROM students s
JOIN orders o ON s.id = o.student_id;

+-------+------------------------------+-----+-------------------+
| name  | formatted_birthdate          | age | days_since_order  |
+-------+------------------------------+-----+-------------------+
| Alex  | March 15, 2008                | 18  | 16                |
| Priya | November 2, 2007              | 18  | 28                |
+-------+------------------------------+-----+-------------------+

This query uses DATEDIFF() to find the total number of days a student has been alive, divides that by 365 and rounds down with FLOOR() to approximate their age in whole years, formats the birthdate nicely with DATE_FORMAT(), and separately calculates how many days have passed since their order.

Common Mistakes

Best Practices

Frequently Asked Questions

Key Takeaways

Summary

Date and time functions let you read the current date, format stored dates nicely, measure the gap between two dates, and shift dates forward or backward — all directly inside SQL.

In this lesson, you learned NOW(), CURDATE(), CURTIME(), DATE_FORMAT(), DATEDIFF(), DATE_ADD(), and DATE_SUB(), and used them together to calculate a student's age and days since an order. Next, you will learn the CASE statement for inline conditional logic.