How to Use EXTRACT Function in MySQL: Syntax and Examples

In MySQL, use the EXTRACT function to get a specific part of a date or time, like year, month, or day. The syntax is EXTRACT(part FROM date), where part is the date or time unit you want to extract.

Syntax

The EXTRACT function extracts a specified part from a date or time value.

• part: The unit to extract, such as YEAR, MONTH, DAY, HOUR, MINUTE, SECOND.
• date: The date or datetime value from which to extract the part.

Format: EXTRACT(part FROM date)

sql

EXTRACT(part FROM date)

Example

This example shows how to extract the year, month, and day from a date value.

sql

SELECT
  EXTRACT(YEAR FROM '2024-06-15') AS year,
  EXTRACT(MONTH FROM '2024-06-15') AS month,
  EXTRACT(DAY FROM '2024-06-15') AS day;

Output

year | month | day
-----|-------|----
2024 | 6     | 15

Common Pitfalls

Common mistakes when using EXTRACT include:

• Using invalid parts like YEARS instead of YEAR.
• Passing a non-date value, which causes errors.
• Confusing EXTRACT with string functions; it only works on date/time types.

sql

/* Wrong: invalid part keyword */
SELECT EXTRACT(YEARS FROM '2024-06-15');

/* Right: correct part keyword */
SELECT EXTRACT(YEAR FROM '2024-06-15');

Quick Reference

Part	Description
YEAR	Extracts the year from a date
MONTH	Extracts the month (1-12)
DAY	Extracts the day of the month (1-31)
HOUR	Extracts the hour (0-23)
MINUTE	Extracts the minute (0-59)
SECOND	Extracts the second (0-59)

Key Takeaways

Use EXTRACT(part FROM date) to get specific date or time parts in MySQL.

Valid parts include YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND.

Pass a valid date or datetime value to avoid errors.

EXTRACT returns a number representing the requested part.

Avoid invalid keywords and non-date inputs.