Most date work in MySQL comes down to eight questions: what is the date now, how far apart are two dates, what date is N days later, how do I show a date nicely, how do I read a date typed as text, which part of the date do I need, how do I group by month, and how do I filter a range safely. This page answers each one with a query you can run and the exact output it returns.
The sample table
CREATE TABLE bookings (
booking_id INT PRIMARY KEY,
guest VARCHAR(20),
booked_at DATETIME,
check_in DATE,
check_out DATE
);
INSERT INTO bookings VALUES
(1,'Ritu', '2026-10-28 09:15:00','2026-11-14','2026-11-17'),
(2,'Kabir','2026-11-02 22:40:00','2026-11-20','2026-11-21'),
(3,'Sana', '2026-11-10 13:05:00','2026-12-30','2027-01-02');
Three types appear here. DATE holds a day, DATETIME holds a day and a time, and TIMESTAMP (not used here) also holds both but converts to and from the session time zone. The MySQL data types guide compares them.
1. What is the current date and time?
NOW() returns the date and time, CURDATE() the date only, and CURTIME() the time only. All three use the session time zone. NOW() returns the same value for every row in one statement. SYSDATE() returns the moment each call actually runs. The results depend on when you run them, so no fixed output is shown.
2. How many days or months between two dates?
SELECT guest,
DATEDIFF(check_out, check_in) AS nights,
DATEDIFF(check_in, DATE(booked_at)) AS days_in_advance
FROM bookings;
| guest | nights | days_in_advance |
|---|---|---|
| Ritu | 3 | 17 |
| Kabir | 1 | 18 |
| Sana | 3 | 50 |
DATEDIFF(a, b) returns a - b in whole days and ignores the time part. For other units, use TIMESTAMPDIFF(unit, start, end). Note that the argument order is reversed compared with DATEDIFF:
SELECT TIMESTAMPDIFF(MONTH, '2025-06-15', '2026-11-16') AS months,
TIMESTAMPDIFF(YEAR, '2001-08-20', '2026-11-16') AS age_years,
TIMESTAMPDIFF(HOUR, '2026-11-15 22:00:00', '2026-11-16 06:30:00') AS hours;
| months | age_years | hours |
|---|---|---|
| 17 | 25 | 8 |
It counts complete units only: 8 hours 30 minutes returns 8. That makes it the right way to calculate age from a date of birth.
3. What date is it N days or months later?
SELECT DATE_ADD('2026-01-31', INTERVAL 1 MONTH) AS plus_month,
DATE_SUB('2026-03-01', INTERVAL 1 DAY) AS day_before,
'2026-11-16' + INTERVAL 45 DAY AS plus_45,
LAST_DAY('2028-02-10') AS feb_end;
| plus_month | day_before | plus_45 | feb_end |
|---|---|---|---|
| 2026-02-28 | 2026-02-28 | 2026-12-31 | 2028-02-29 |
Adding a month to 31 January gives 28 February: MySQL moves to the last valid day instead of spilling into March. LAST_DAY() handles leap years (2028 is one). That is handy for "bill on the last day of the month" logic.
4. How do I display a date the Indian way?
SELECT guest,
DATE_FORMAT(booked_at, '%d %b %Y, %h:%i %p') AS booked_on,
DATE_FORMAT(check_in, '%W') AS check_in_day
FROM bookings;
| guest | booked_on | check_in_day |
|---|---|---|
| Ritu | 28 Oct 2026, 09:15 AM | Saturday |
| Kabir | 02 Nov 2026, 10:40 PM | Friday |
| Sana | 10 Nov 2026, 01:05 PM | Wednesday |
| Specifier | Meaning | Specifier | Meaning |
|---|---|---|---|
%d | day 01–31 | %H | hour 00–23 |
%m | month 01–12 | %h | hour 01–12 |
%b / %M | Oct / October | %i | minutes (not %M) |
%Y / %y | 2026 / 26 | %p | AM / PM |
%W / %a | Saturday / Sat | %s | seconds |
A common slip is to use %M for minutes. It prints the month name.
5. How do I turn "16/11/2026" into a real date?
SELECT STR_TO_DATE('16/11/2026', '%d/%m/%Y') AS from_indian_format,
STR_TO_DATE('16-11-2026 18:45', '%d-%m-%Y %H:%i') AS with_time,
STR_TO_DATE('31/02/2026', '%d/%m/%Y') AS invalid;
| from_indian_format | with_time | invalid |
|---|---|---|
| 2026-11-16 | 2026-11-16 18:45:00 | NULL |
Use this when you load CSV files typed in DD/MM/YYYY format. An impossible date returns NULL with a warning instead of a wrong date, so check for NULLs after an import. Loading such files is covered in importing CSV data into MySQL.
6. How do I pull out the year, month or hour?
SELECT guest, YEAR(check_in) AS y, MONTH(check_in) AS m, DAY(check_in) AS d,
EXTRACT(HOUR FROM booked_at) AS booked_hour, QUARTER(check_in) AS q
FROM bookings;
| guest | y | m | d | booked_hour | q |
|---|---|---|---|---|---|
| Ritu | 2026 | 11 | 14 | 9 | 4 |
| Kabir | 2026 | 11 | 20 | 22 | 4 |
| Sana | 2026 | 12 | 30 | 13 | 4 |
Watch the weekday functions. For Monday 16 November 2026, WEEKDAY() returns 0 (Monday = 0) but DAYOFWEEK() returns 2 (Sunday = 1). DAYNAME() returns 'Monday'. QUARTER() follows the calendar year; for an April–March financial year, compute the quarter yourself.
7. How do I group by month?
SELECT DATE_FORMAT(booked_at, '%Y-%m') AS mon, COUNT(*) AS bookings
FROM bookings
GROUP BY mon
ORDER BY mon;
| mon | bookings |
|---|---|
| 2026-10 | 1 |
| 2026-11 | 2 |
Use '%Y-%m', not '%b': the year-month string sorts correctly and keeps October 2026 apart from October 2027. More grouping patterns are in the GROUP BY clause explained.
8. How do I filter a date range without missing rows?
SELECT guest FROM bookings
WHERE booked_at BETWEEN '2026-10-28' AND '2026-11-10';
| guest |
|---|
| Ritu |
| Kabir |
Sana booked on 10 November, yet she is missing. '2026-11-10' means midnight at the start of that day, so 13:05 falls outside the range. Use a half-open range instead. It is also index-friendly, unlike WHERE MONTH(booked_at) = 11:
SELECT guest FROM bookings
WHERE booked_at >= '2026-11-01' AND booked_at < '2026-12-01';
| guest |
|---|
| Kabir |
| Sana |
Time zones
Servers often run in UTC while users are in IST. Offsets work without extra setup:
SELECT CONVERT_TZ('2026-11-16 04:30:00', '+00:00', '+05:30') AS ist;
| ist |
|---|
| 2026-11-16 10:00:00 |
Named zones such as 'Asia/Kolkata' only work after the time zone tables are loaded on the server. Without them, CONVERT_TZ returns NULL. India has no daylight saving time, so a fixed +05:30 offset is safe for IST.
For text manipulation alongside dates, see MySQL string functions.