EXTRACT and YEAR/MONTH/DAY in MySQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the EXTRACT function do in MySQL?

The EXTRACT function retrieves a specific part (like year, month, or day) from a date or datetime value.

beginner

How do you extract the year from a date column named 'order_date'?

Use EXTRACT(YEAR FROM order_date) to get the year part from the date.

beginner

Which keyword would you use with EXTRACT to get the month from a date?

Use the keyword MONTH with EXTRACT, like EXTRACT(MONTH FROM date_column).

beginner

Write a query to get the day part from a datetime column named 'created_at'.

SELECT EXTRACT(DAY FROM created_at) AS day_part FROM your_table;

beginner

Can EXTRACT be used on both DATE and DATETIME types?

Yes, EXTRACT works on both DATE and DATETIME types to get parts like year, month, day, hour, minute, and second.

Which of the following extracts the year from a date column 'birth_date'?

A YEAR(birth_date)

B GETYEAR(birth_date)

C EXTRACT(YEAR FROM birth_date)

D DATEPART(YEAR, birth_date)

EXTRACT(YEAR FROM birth_date) is the standard SQL syntax to extract the year part.

What will EXTRACT(MONTH FROM '2024-06-15') return?

A 6

B NULL

C 2024

D 15

It returns 6 because the month part of the date is June (6).

Which part does EXTRACT(DAY FROM '2023-12-01') return?

A 1

B 12

C 2023

D NULL

It returns 1 because the day part of the date is the 1st.

Is EXTRACT valid for extracting hour from a DATETIME value?

A Only for DATE type

B Yes

C No

D Only for TIMESTAMP type

EXTRACT can extract hour, minute, second from DATETIME values.

What does EXTRACT(YEAR FROM NULL) return?

A 0

B Current year

C Error

D NULL

EXTRACT returns NULL when the input date is NULL.

Explain how to use the EXTRACT function to get the year, month, and day from a date in MySQL.

Think about the format EXTRACT(part FROM date).

Describe the difference between extracting parts from DATE and DATETIME types using EXTRACT.

Consider what extra parts DATETIME has compared to DATE.

Practice

1. What does the MySQL function EXTRACT(YEAR FROM '2023-07-15') return?

easy

A. 07

B. '2023-07-15'

C. 15

D. 2023

Solution

1. Step 1: Understand EXTRACT function usage

   The EXTRACT function retrieves a specific part of a date, here the YEAR part.

2. Step 2: Apply EXTRACT to the given date

   Extracting YEAR from '2023-07-15' returns 2023 as a number.

3. Final Answer:

   2023 -> Option D

4. Quick Check:

   EXTRACT(YEAR FROM date) = year number [OK]

Hint: EXTRACT(YEAR FROM date) gives the year number [OK]

Common Mistakes:

• Confusing YEAR with MONTH or DAY
• Expecting a string instead of a number
• Using EXTRACT without FROM keyword

2. Which of the following is the correct syntax to extract the month from a date column named order_date in MySQL?

easy

A. EXTRACT(MONTH FROM order_date)

B. EXTRACT(order_date FROM MONTH)

C. EXTRACT(MONTH order_date)

D. EXTRACT(MONTH, order_date)

Solution

1. Step 1: Recall EXTRACT syntax

   The correct syntax is EXTRACT(part FROM date), where part is YEAR, MONTH, or DAY.

2. Step 2: Match syntax to options

   EXTRACT(MONTH FROM order_date) uses EXTRACT(MONTH FROM order_date), which is correct syntax.

3. Final Answer:

   EXTRACT(MONTH FROM order_date) -> Option A

4. Quick Check:

   EXTRACT(part FROM date) syntax is correct [OK]

Hint: Use EXTRACT(part FROM date) syntax exactly [OK]

Common Mistakes:

• Omitting FROM keyword
• Swapping arguments order
• Using commas instead of FROM

3. Given the table events with a column event_date storing dates, what will this query return?

SELECT EXTRACT(DAY FROM event_date) AS day FROM events WHERE EXTRACT(MONTH FROM event_date) = 12;

medium

A. Syntax error due to wrong EXTRACT usage

B. All days of events that happened in December

C. All years of events that happened in December

D. All months of events that happened in December

Solution

1. Step 1: Understand the WHERE clause

   The WHERE clause filters rows where the month part of event_date is 12 (December).

2. Step 2: Understand the SELECT clause

   The SELECT clause extracts the day part from event_date for those filtered rows.

3. Final Answer:

   All days of events that happened in December -> Option B

4. Quick Check:

   EXTRACT(MONTH) filters December, EXTRACT(DAY) selects day [OK]

Hint: Filter by EXTRACT(MONTH), select EXTRACT(DAY) for days [OK]

Common Mistakes:

• Confusing day and month extraction
• Thinking EXTRACT returns strings
• Assuming syntax error without checking carefully

4. Identify the error in this query:

SELECT EXTRACT(YEAR order_date) FROM orders;

medium

A. EXTRACT cannot be used with YEAR

B. order_date should be in quotes

C. Missing FROM keyword after YEAR

D. No error, query is correct

Solution

1. Step 1: Check EXTRACT syntax

   EXTRACT requires the format EXTRACT(part FROM date), so FROM keyword is mandatory.

2. Step 2: Identify missing FROM keyword

   The query misses FROM between YEAR and order_date, causing syntax error.

3. Final Answer:

   Missing FROM keyword after YEAR -> Option C

4. Quick Check:

   EXTRACT(part FROM date) needs FROM [OK]

Hint: Always include FROM between part and date in EXTRACT [OK]

Common Mistakes:

• Omitting FROM keyword
• Putting column name in quotes
• Thinking EXTRACT can't use YEAR

5. You want to count how many orders were placed each year and month from the orders table with a created_at datetime column. Which query correctly groups by year and month using EXTRACT?

hard

A. SELECT EXTRACT(YEAR FROM created_at) AS year, EXTRACT(MONTH FROM created_at) AS month, COUNT(*) FROM orders GROUP BY year, month;

B. SELECT YEAR(created_at) AS year, MONTH(created_at) AS month, COUNT(*) FROM orders GROUP BY year, month;

C. SELECT EXTRACT(YEAR, created_at) AS year, EXTRACT(MONTH, created_at) AS month, COUNT(*) FROM orders GROUP BY year, month;

D. SELECT EXTRACT(YEAR FROM created_at) AS year, MONTH(created_at) AS month, COUNT(*) FROM orders GROUP BY year, month;

Solution

1. Step 1: Understand grouping by year and month

   We need to extract year and month parts from created_at to group orders by these parts.

2. Step 2: Check EXTRACT syntax correctness

   SELECT EXTRACT(YEAR FROM created_at) AS year, EXTRACT(MONTH FROM created_at) AS month, COUNT(*) FROM orders GROUP BY year, month; uses correct EXTRACT syntax for both year and month and groups by aliases year and month.

3. Step 3: Verify other options

   SELECT YEAR(created_at) AS year, MONTH(created_at) AS month, COUNT(*) FROM orders GROUP BY year, month; uses YEAR() and MONTH() functions which also work but question asks for EXTRACT usage. SELECT EXTRACT(YEAR, created_at) AS year, EXTRACT(MONTH, created_at) AS month, COUNT(*) FROM orders GROUP BY year, month; has wrong EXTRACT syntax. SELECT EXTRACT(YEAR FROM created_at) AS year, MONTH(created_at) AS month, COUNT(*) FROM orders GROUP BY year, month; mixes EXTRACT and MONTH() inconsistently.

4. Final Answer:

   Option A query with correct EXTRACT syntax and grouping -> Option A

5. Quick Check:

   Use EXTRACT(part FROM date) consistently for grouping [OK]

Hint: Use EXTRACT(part FROM date) for both year and month [OK]

Common Mistakes:

• Using wrong EXTRACT syntax with commas
• Mixing EXTRACT and other date functions inconsistently
• Not grouping by extracted aliases