SQL Tutorial › Functions & Grouping › Date & Time Functions

SQL Date and Time Functions

Q: How do I get the current date in MySQL?

CURDATE() returns the current date; NOW() returns the date and time.

Q: How do I calculate the difference between two dates in SQL?

In MySQL use DATEDIFF(end, start) for days, or TIMESTAMPDIFF(unit, start, end) for months, years, hours and other units.

Q: How do I format a date in MySQL?

Use DATE_FORMAT(date, format), for example DATE_FORMAT(order_date, '%d/%m/%Y').

Q: How do I group rows by month in SQL?

Group by DATE_FORMAT(date_col, '%Y-%m'), or by YEAR(date_col), MONTH(date_col).

IntermediateLesson 28 of 6913 min read9 queries run on MySQL 8.4

Dates drive most business questions: orders this month, customers who joined last year, employees’ years of service, days until delivery. MySQL has a rich set of date and time functions. This lesson shows the ones you will reach for every week, run on the sample orders and employees.

In this lesson

Get the current date and time
Extract parts with YEAR, MONTH, DAYNAME and EXTRACT
Calculate with DATE_ADD, DATEDIFF and TIMESTAMPDIFF
Format with DATE_FORMAT and group by month

Current date and time

MySQL

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

ResultMySQL 8.4

NOW()	CURDATE()	CURTIME()	UTC_TIMESTAMP()
2026-11-02 09:34:54	2026-11-02	09:34:54	2026-11-02 09:34:54

1 row in set

Function	Returns
`NOW()`	date and time when the statement started
`CURDATE()`	date only
`CURTIME()`	time only
`UTC_TIMESTAMP()`	date and time in UTC (the server above runs in UTC, so it equals `NOW()`)

The result above is from the moment this page was built. The values change every time you run the query, so the examples below use fixed dates and the dates stored in the sample tables.

Sample data

orders · 10 rows

order_id	customer_id	order_date	status
1001	1	2025-01-05	Delivered
1002	2	2025-01-17	Delivered
1003	1	2025-02-02	Delivered
1004	3	2025-02-14	Cancelled
1005	4	2025-03-08	Delivered
1006	5	2025-03-22	Shipped
1007	2	2025-04-10	Delivered
1008	1	2025-04-28	Pending
1009	3	2025-05-15	Delivered
1010	5	2025-06-01	Delivered

Extract parts of a date

MySQL

SELECT order_id, order_date,       YEAR(order_date)    AS yr,       MONTH(order_date)   AS mon,       MONTHNAME(order_date) AS month_name,       DAY(order_date)     AS dy,       DAYNAME(order_date) AS weekday,       QUARTER(order_date) AS qtrFROM ordersLIMIT 4;

ResultMySQL 8.4

order_id	order_date	yr	mon	month_name	dy	weekday	qtr
1001	2025-01-05	2025	1	January	5	Sunday	1
1002	2025-01-17	2025	1	January	17	Friday	1
1003	2025-02-02	2025	2	February	2	Sunday	1
1004	2025-02-14	2025	2	February	14	Friday	1

4 rows in set

How this query works, step by step

YEAR, MONTH and DAY return numbers.
MONTHNAME and DAYNAME return English names.
QUARTER returns 1 to 4. EXTRACT(MONTH FROM order_date) is the standard-SQL way to write MONTH(order_date).

Add and subtract time

Expected delivery is 5 days after the order; the return window ends 1 month after delivery:

MySQL

SELECT order_id, order_date,       DATE_ADD(order_date, INTERVAL 5 DAY)                    AS expected_delivery,       order_date + INTERVAL 5 DAY + INTERVAL 1 MONTH           AS return_deadline,       DATE_SUB(order_date, INTERVAL 1 WEEK)                    AS week_beforeFROM ordersWHERE order_id IN (1001, 1002, 1004);

ResultMySQL 8.4

order_id	order_date	expected_delivery	return_deadline	week_before
1001	2025-01-05	2025-01-10	2025-02-10	2024-12-29
1002	2025-01-17	2025-01-22	2025-02-22	2025-01-10
1004	2025-02-14	2025-02-19	2025-03-19	2025-02-07

3 rows in set

How this query works, step by step

DATE_ADD(date, INTERVAL n unit) adds; DATE_SUB subtracts.
date + INTERVAL ... is a shorter way to write the same thing.
Units include DAY, WEEK, MONTH, QUARTER, YEAR, HOUR, MINUTE and SECOND.

Difference between dates

Years of service on a fixed reference date, 31 Dec 2025:

MySQL

SELECT first_name, hire_date,       DATEDIFF('2025-12-31', hire_date)                AS days_employed,       TIMESTAMPDIFF(MONTH, hire_date, '2025-12-31')     AS months_employed,       TIMESTAMPDIFF(YEAR, hire_date, '2025-12-31')      AS full_yearsFROM employeesORDER BY hire_dateLIMIT 5;

ResultMySQL 8.4

first_name	hire_date	days_employed	months_employed	full_years
Meera	2015-01-05	4013	131	10
Vikram	2017-11-20	2963	97	8
Asha	2018-04-12	2820	92	7
Sneha	2019-02-25	2501	82	6
Rahul	2019-07-01	2375	77	6

5 rows in set

How this query works, step by step

DATEDIFF(later, earlier) returns whole days.
TIMESTAMPDIFF(unit, earlier, later) counts complete units. Note the reversed argument order.
TIMESTAMPDIFF(YEAR, ...) counts only full years, so it is the right way to calculate age or service.

Tip

In a live system, replace '2025-12-31' with CURDATE().

Format dates with DATE_FORMAT

MySQL

SELECT order_date,       DATE_FORMAT(order_date, '%d/%m/%Y')     AS indian_style,       DATE_FORMAT(order_date, '%d %b %Y')     AS short_text,       DATE_FORMAT(order_date, '%W, %M %e')    AS long_text,       DATE_FORMAT(order_date, '%Y-%m')        AS month_labelFROM ordersWHERE order_id IN (1001, 1006);

ResultMySQL 8.4

order_date	indian_style	short_text	long_text	month_label
2025-01-05	05/01/2025	05 Jan 2025	Sunday, January 5	2025-01
2025-03-22	22/03/2025	22 Mar 2025	Saturday, March 22	2025-03

2 rows in set

Note

year_month would be a natural alias here, but it is a reserved word in MySQL (it is an INTERVAL unit), so it would cause a syntax error.

Specifier	Meaning	Example
`%Y` / `%y`	4- / 2-digit year	2025 / 25
`%m` / `%c`	month 01-12 / 1-12	03 / 3
`%M` / `%b`	month name / short	March / Mar
`%d` / `%e`	day 01-31 / 1-31	08 / 8
`%W` / `%a`	weekday / short	Saturday / Sat
`%H:%i:%s`	24-hour time	14:05:09

STR_TO_DATE does the reverse, turning text into a date:

MySQL

SELECT STR_TO_DATE('31/01/2026', '%d/%m/%Y') AS parsed_date;

ResultMySQL 8.4

parsed_date
2026-01-31

1 row in set

Group by month

Orders and revenue per month, the most common date report:

MySQL

SELECT DATE_FORMAT(o.order_date, '%Y-%m') AS month,       COUNT(DISTINCT o.order_id)          AS orders,       SUM(oi.quantity * p.price)          AS revenueFROM orders oJOIN order_items oi ON oi.order_id = o.order_idJOIN products p     ON p.product_id = oi.product_idWHERE o.status <> 'Cancelled'GROUP BY monthORDER BY month;

ResultMySQL 8.4

month	orders	revenue
2025-01	2	13743
2025-02	1	4298
2025-03	2	8783
2025-04	2	21795
2025-05	1	3499
2025-06	1	1145

6 rows in set

How this query works, step by step

Each order date becomes a 'YYYY-MM' label.
Rows with the same label form one group.
COUNT(DISTINCT o.order_id) counts orders, not lines; SUM totals the revenue.

Filter a date range efficiently

Orders in March 2025:

SQL

SELECT order_id, order_dateFROM ordersWHERE order_date >= '2025-03-01' AND order_date < '2025-04-01';

ResultMySQL 8.4

order_id	order_date
1005	2025-03-08
1006	2025-03-22

2 rows in set

Watch out

WHERE MONTH(order_date) = 3 gives the same rows here, but wrapping the column in a function stops MySQL from using an index on order_date, and it would also match March of every year. Use a range on the raw column.

Useful helpers

MySQL

SELECT LAST_DAY('2024-02-10')                      AS last_day_feb_2024,       DAYOFWEEK('2025-03-08')                     AS day_of_week,       WEEK('2025-03-08')                          AS week_no,       DATE('2025-03-08 17:45:00')                 AS date_part,       MAKEDATE(2026, 32)                          AS day_32_of_2026;

ResultMySQL 8.4

last_day_feb_2024	day_of_week	week_no	date_part	day_32_of_2026
2024-02-29	7	9	2025-03-08	2026-02-01

1 row in set

How this query works, step by step

LAST_DAY returns the last day of the month: 2024 is a leap year, so 29 February.
DAYOFWEEK returns 1 for Sunday to 7 for Saturday; 8 March 2025 was a Saturday.
DATE() strips the time from a datetime.
MAKEDATE(year, n) builds the nth day of a year: day 32 is 1 February.

Exercise: Orders per weekday

Count the orders placed on each weekday name.

Expected resultMySQL 8.4

weekday	orders
Sunday	3
Friday	2
Saturday	2
Thursday	2
Monday	1

5 rows in set

Hint 1

Group by DAYNAME(order_date).

MySQL

SELECT DAYNAME(order_date) AS weekday, COUNT(*) AS ordersFROM ordersGROUP BY weekdayORDER BY orders DESC, weekday;

ResultMySQL 8.4

weekday	orders
Sunday	3
Friday	2
Saturday	2
Thursday	2
Monday	1

5 rows in set

Exercise: Joined in 2024

List customers who joined in 2024, with the month name they joined.

Expected resultMySQL 8.4

customer_name	joined_on	joined_month
Ananya Sharma	2024-01-15	January
Rohit Kumar	2024-02-03	February
Fatima Khan	2024-03-21	March
David Fernandes	2024-05-09	May
Ishita Bose	2024-07-30	July

5 rows in set

Hint 1

Filter with a date range, show MONTHNAME(joined_on).

MySQL

SELECT customer_name, joined_on, MONTHNAME(joined_on) AS joined_monthFROM customersWHERE joined_on >= '2024-01-01' AND joined_on < '2025-01-01';

ResultMySQL 8.4

customer_name	joined_on	joined_month
Ananya Sharma	2024-01-15	January
Rohit Kumar	2024-02-03	February
Fatima Khan	2024-03-21	March
David Fernandes	2024-05-09	May
Ishita Bose	2024-07-30	July

5 rows in set

Quick quiz

1. Which function returns today’s date without the time?

CURDATE() returns only the date.

2. What does DATEDIFF('2025-03-10', '2025-03-01') return?

3. Which is the best way to calculate full years of service?

4. Which format string produces 08/03/2025?

%d day, %m month, %Y four-digit year.

Date functions FAQ

How do I get the current date in MySQL?

CURDATE() returns the current date; NOW() returns the date and time.

How do I calculate the difference between two dates in SQL?

In MySQL use DATEDIFF(end, start) for days, or TIMESTAMPDIFF(unit, start, end) for months, years, hours and other units.

How do I format a date in MySQL?

Use DATE_FORMAT(date, format), for example DATE_FORMAT(order_date, '%d/%m/%Y').

How do I group rows by month in SQL?

Group by DATE_FORMAT(date_col, '%Y-%m'), or by YEAR(date_col), MONTH(date_col).