Practical 5: Date Functions

Chapter Forty-One

Syllabus topic Module 2, Practical 5: "Queries involving Date Functions"

Pages 153 to 156 of 206

Aim

To write queries involving date functions.

Why a date must be a DATE

A date kept in a VARCHAR is a piece of text. Text cannot be subtracted from text, and text sorts alphabetically, so '09-01-2025' comes before '10-12-2024'. A DATE column can be compared, subtracted, added to and sorted, and every function in this chapter works on it.

MySQL's format is YYYY-MM-DD, always, and it is the ISO 8601 order: largest unit first. That order is why a DATE sorts correctly even as text, and it is the reason the standard chose it.

Today, and now

SELECT CURDATE(), CURRENT_DATE, NOW(), CURTIME();

That listing really ran; its result is not printed here because it would be different tomorrow.

Function	Returns	Looks like
`CURDATE()`, `CURRENT_DATE`	today's date	`2025-07-21`
`NOW()`, `CURRENT_TIMESTAMP`	date and time	`2025-07-21 14:30:00`
`CURTIME()`	the time of day	`14:30:00`
`SYSDATE()`	the time at the moment it is called	as `NOW()`

NOW() and SYSDATE() differ in a way worth one line: NOW() gives the time the statement started, so it is the same everywhere in one statement; SYSDATE() gives the time it was called, so two of them in one statement can disagree. Use NOW().

Pulling a date apart

SELECT issued_on,
       YEAR(issued_on)  AS yr,
       MONTH(issued_on) AS mth,
       DAY(issued_on)   AS dy
FROM loan WHERE loan_id <= 3;

+------------+------+------+------+
| issued_on  | yr   | mth  | dy   |
+------------+------+------+------+
| 2025-06-02 | 2025 |    6 |    2 |
| 2025-06-02 | 2025 |    6 |    2 |
| 2025-06-10 | 2025 |    6 |   10 |
+------------+------+------+------+

And the parts that have names rather than numbers:

SELECT issued_on,
       MONTHNAME(issued_on) AS month_name,
       DAYNAME(issued_on)   AS day_name,
       QUARTER(issued_on)   AS qtr
FROM loan WHERE loan_id = 1;

+------------+------------+----------+------+
| issued_on  | month_name | day_name | qtr  |
+------------+------------+----------+------+
| 2025-06-02 | June       | Monday   |    2 |
+------------+------------+----------+------+

DAYNAME is genuinely useful: a library that closes on Sundays can find loans issued on one without anybody working out the day by hand.

The difference between two dates

SELECT loan_id, issued_on, returned_on,
       DATEDIFF(returned_on, issued_on) AS days_kept
FROM loan
WHERE returned_on IS NOT NULL
ORDER BY loan_id;

+---------+------------+-------------+-----------+
| loan_id | issued_on  | returned_on | days_kept |
+---------+------------+-------------+-----------+
|       1 | 2025-06-02 | 2025-06-14  |        12 |
|       2 | 2025-06-02 | 2025-06-30  |        28 |
|       3 | 2025-06-10 | 2025-06-20  |        10 |
|       5 | 2025-07-05 | 2025-07-15  |        10 |
|       6 | 2025-07-08 | 2025-07-19  |        11 |
+---------+------------+-------------+-----------+

DATEDIFF(a, b) is a minus b, in whole days, and the order matters: the other way round gives a negative number. It counts days, not hours, so a loan issued and returned on the same day is 0.

Practical 5: Date Functions

TIMESTAMPDIFF does the same in a unit you choose, which is how an age or a length of membership is worked out:

SELECT member_name, joined_on,
       TIMESTAMPDIFF(MONTH, joined_on, '2025-08-01') AS months_a_member
FROM member ORDER BY member_id LIMIT 4;

+---------------+------------+-----------------+
| member_name   | joined_on  | months_a_member |
+---------------+------------+-----------------+
| Asha Kulkarni | 2024-07-15 |              12 |
| Ravi Deshmukh | 2024-07-18 |              12 |
| Meena Iyer    | 2024-08-02 |              11 |
| Imran Shaikh  | 2024-08-09 |              11 |
+---------------+------------+-----------------+

TIMESTAMPDIFF(unit, a, b) is b minus a, which is the opposite way round from DATEDIFF. That is genuinely confusing and it is worth writing in your journal. The units are DAY, WEEK, MONTH, QUARTER, YEAR, HOUR, MINUTE and SECOND.

Adding and subtracting

SELECT issued_on,
       DATE_ADD(issued_on, INTERVAL 14 DAY) AS due_on,
       DATE_SUB(issued_on, INTERVAL 1 WEEK) AS a_week_before
FROM loan WHERE loan_id = 1;

+------------+------------+---------------+
| issued_on  | due_on     | a_week_before |
+------------+------------+---------------+
| 2025-06-02 | 2025-06-16 | 2025-05-26    |
+------------+------------+---------------+

INTERVAL 14 DAY is one thing, not two: a number and a unit written together. The units are the same list as for TIMESTAMPDIFF.

ADDDATE and SUBDATE are other names for the same two functions, and issued_on + INTERVAL 14 DAY also works. All three forms are accepted; use whichever your teacher uses.

The overdue query, which is what the practical is for

The library lends for fourteen days and charges two rupees a day after that.

SELECT l.loan_id, b.title,
       l.issued_on,
       DATE_ADD(l.issued_on, INTERVAL 14 DAY) AS due_on,
       l.returned_on,
       DATEDIFF(l.returned_on, DATE_ADD(l.issued_on, INTERVAL 14 DAY)) AS days_late
FROM loan l JOIN book b ON b.book_id = l.book_id
WHERE l.returned_on IS NOT NULL
ORDER BY l.loan_id;

+---------+------------------+------------+------------+-------------+-----------+
| loan_id | title            | issued_on  | due_on     | returned_on | days_late |
+---------+------------------+------------+------------+-------------+-----------+
|       1 | The C Language   | 2025-06-02 | 2025-06-16 | 2025-06-14  |        -2 |
|       2 | Database Systems | 2025-06-02 | 2025-06-16 | 2025-06-30  |        14 |
|       3 | MySQL Reference  | 2025-06-10 | 2025-06-24 | 2025-06-20  |        -4 |
|       5 | The C Language   | 2025-07-05 | 2025-07-19 | 2025-07-15  |        -4 |
|       6 | Learn SQL        | 2025-07-08 | 2025-07-22 | 2025-07-19  |        -3 |
+---------+------------------+------------+------------+-------------+-----------+

One loan is late and the rest are not, and the negative numbers are the days to spare. Turning that into a fine means charging nothing when the book was on time, which is what GREATEST does:

SELECT l.loan_id, b.title,
       GREATEST(DATEDIFF(l.returned_on,
                DATE_ADD(l.issued_on, INTERVAL 14 DAY)), 0) * 2 AS fine
FROM loan l JOIN book b ON b.book_id = l.book_id
WHERE l.returned_on IS NOT NULL
ORDER BY l.loan_id;

+---------+------------------+------+
| loan_id | title            | fine |
+---------+------------------+------+
|       1 | The C Language   |    0 |
|       2 | Database Systems |   28 |
|       3 | MySQL Reference  |    0 |
|       5 | The C Language   |    0 |
|       6 | Learn SQL        |    0 |
+---------+------------------+------+

Practical 5: Date Functions

GREATEST(x, 0) is the larger of the two, so a negative number becomes 0 and nobody is paid for returning a book early. It is the SQL of the if a student would otherwise write in a program, and doing it in the query is the point of the practical.

For the books still out, the due date is compared with CURDATE():

SELECT loan_id, issued_on,
       GREATEST(DATEDIFF(CURDATE(),
                DATE_ADD(issued_on, INTERVAL 14 DAY)), 0) * 2 AS fine_so_far
FROM loan WHERE returned_on IS NULL;

That one really ran too, and its answer grows by two rupees every day, which is why it is not printed here.

Formatting a date for a person to read

SELECT issued_on,
       DATE_FORMAT(issued_on, '%d-%m-%Y')      AS indian,
       DATE_FORMAT(issued_on, '%d %M %Y')      AS long_form,
       DATE_FORMAT(issued_on, '%W, %e %b %y')  AS with_day
FROM loan WHERE loan_id = 1;

+------------+------------+--------------+------------------+
| issued_on  | indian     | long_form    | with_day         |
+------------+------------+--------------+------------------+
| 2025-06-02 | 02-06-2025 | 02 June 2025 | Monday, 2 Jun 25 |
+------------+------------+--------------+------------------+

Code	Means
`%d`	day, two digits
`%e`	day, no leading zero
`%m`	month, two digits
`%b`	month, short name
`%M`	month, full name
`%y`	year, two digits
`%Y`	year, four digits
`%W`	weekday, full name
`%H:%i:%s`	hours, minutes, seconds

DATE_FORMAT produces text, not a date. Its result cannot be sorted as a date or subtracted from another date, so format at the very end, in the SELECT, and never store the formatted version.

Going the other way, STR_TO_DATE reads text into a date with the same codes:

SELECT STR_TO_DATE('21-07-2025', '%d-%m-%Y') AS a_real_date;

+-------------+
| a_real_date |
+-------------+
| 2025-07-21  |
+-------------+

That is what you use when somebody hands you a file of dates written the Indian way round.

Two more that answer real questions

SELECT LAST_DAY('2025-02-10') AS end_of_feb,
       LAST_DAY('2024-02-10') AS end_of_feb_leap,
       DAYOFYEAR('2025-03-01') AS day_number;

+------------+-----------------+------------+
| end_of_feb | end_of_feb_leap | day_number |
+------------+-----------------+------------+
| 2025-02-28 | 2024-02-29      |         60 |
+------------+-----------------+------------+

LAST_DAY knows about leap years, which is the leap year rule of [Practical 1(c): Is This a Leap Year?] solved once by the server so that nobody has to write it again.

What beginners get wrong

Storing a date in a VARCHAR. Nothing in this chapter then works.

Writing a date as '21-07-2025'. MySQL wants '2025-07-21'. Use STR_TO_DATE for input in another order.

Getting DATEDIFF's arguments the wrong way round. It is the first minus the second.

Expecting TIMESTAMPDIFF to take them in the same order. It does not: it is the second minus the first.

Practical 5: Date Functions

Writing INTERVAL 14 DAYS. The unit is singular: DAY.

Sorting on a DATE_FORMAT result. It is text, so 09 January sorts before 10 December.

Forgetting that a date arithmetic with NULL gives NULL. A loan that has not come back has no days_kept, and the row shows NULL rather than being missing.

Quick revision

Dates are YYYY-MM-DD; store them in DATE, never in VARCHAR.
CURDATE() today, NOW() date and time, CURTIME() the time.
YEAR, MONTH, DAY, MONTHNAME, DAYNAME, QUARTER pull a date apart.
DATEDIFF(a, b) is a minus b in days.
TIMESTAMPDIFF(unit, a, b) is b minus a, in the unit named.
DATE_ADD(d, INTERVAL n DAY) and DATE_SUB; the unit is singular.
GREATEST(x, 0) turns a negative number into zero, which is how a fine stops at nothing.
DATE_FORMAT(d, '%d-%m-%Y') makes text; STR_TO_DATE(s, '%d-%m-%Y') reads it back.
LAST_DAY knows about leap years.

What goes in your journal

Aim, then one query per group: the parts of a date, a DATEDIFF, a DATE_ADD, a DATE_FORMAT and a STR_TO_DATE. Then the overdue query in full, with the due date, the days late and the fine in the same result.

The overdue query is the one to spend time on. It uses four ideas at once, it answers a question a real library asks, and it is the query an examiner will set.

Test yourself

1. In what format does MySQL expect a date literal? 'YYYY-MM-DD', year first.

2. DATEDIFF('2025-06-14', '2025-06-02') gives what?

It is the first date minus the second, in whole days.

3. How do you add fourteen days to a date? DATE_ADD(d, INTERVAL 14 DAY), or equivalently d + INTERVAL 14 DAY.

4. Why should a DATE_FORMAT result never be stored in a column? Because it is text. It cannot be compared or subtracted as a date, and it sorts alphabetically rather than chronologically.

5. How would you charge two rupees a day for a late book and nothing for one returned on time? GREATEST(DATEDIFF(returned_on, due_on), 0) * 2, so that a negative number of days becomes zero.

6. Which is the second minus the first: DATEDIFF or TIMESTAMPDIFF? TIMESTAMPDIFF. DATEDIFF is the first minus the second.

The rest of this subject

These notes are cut from the University's printed syllabus. Open the syllabus itself for the same subject.

Syllabus for Major Practical 1

Report or request