DATE_FORMAT and EXTRACT in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this DATE_FORMAT query?

Given a table events with a column event_date of type DATE, what is the output of the following query?

SELECT DATE_FORMAT(event_date, '%Y-%m-%d') AS formatted_date FROM events WHERE event_id = 1;

SQL

SELECT DATE_FORMAT(event_date, '%Y-%m-%d') AS formatted_date FROM events WHERE event_id = 1;

A '2023/15/07'

B '07/15/2023'

C '15-07-2023'

D '2023-07-15'

💡 Hint

The format string '%Y-%m-%d' means year-month-day with dashes.

DATE_FORMAT formats the date according to the pattern. '%Y' is 4-digit year, '%m' is 2-digit month, '%d' is 2-digit day, separated by dashes.

❓ query_result

intermediate

What does EXTRACT return for this date?

Consider the query:

SELECT EXTRACT(MONTH FROM '2024-02-29'::DATE) AS month_value;

What is the value of month_value?

SQL

SELECT EXTRACT(MONTH FROM '2024-02-29'::DATE) AS month_value;

A 2

B 29

C 2024

D NULL

💡 Hint

EXTRACT(MONTH FROM date) returns the month number from the date.

The date is February 29, 2024. EXTRACT(MONTH FROM date) returns 2 because February is the second month.

📝 Syntax

advanced

Which query correctly extracts the year from a timestamp?

You want to extract the year from a timestamp column named created_at. Which of the following queries is syntactically correct and returns the year as an integer?

A SELECT EXTRACT('YEAR' FROM created_at) FROM orders;

B SELECT EXTRACT(YEAR FROM created_at) FROM orders;

C SELECT EXTRACT(YEAR, created_at) FROM orders;

D SELECT EXTRACT(created_at, YEAR) FROM orders;

💡 Hint

EXTRACT uses the syntax EXTRACT(field FROM source). The field is not a string.

Option B uses the correct syntax: EXTRACT(YEAR FROM created_at). Options A, C, and D are invalid syntax.

❓ optimization

advanced

Optimizing date filtering with EXTRACT vs DATE_FORMAT

You want to select all rows from sales where the sale happened in March 2023. Which query is more efficient and why?

A SELECT * FROM sales WHERE EXTRACT(YEAR FROM sale_date) = 2023 AND EXTRACT(MONTH FROM sale_date) = 3;

B SELECT * FROM sales WHERE DATE_FORMAT(sale_date, '%Y-%m') = '2023-03';

C SELECT * FROM sales WHERE sale_date >= '2023-03-01' AND sale_date < '2023-04-01';

D SELECT * FROM sales WHERE sale_date LIKE '2023-03%';

💡 Hint

Filtering by range on the raw date column can use indexes better than functions on the column.

Option C uses a range condition on the raw date column, which can use indexes efficiently. Options B and C apply functions on the column, preventing index use. Option C uses LIKE which is less efficient and may not use indexes.

🔧 Debug

expert

Why does this EXTRACT query raise an error?

Given the query:

SELECT EXTRACT(QUARTER FROM '2023-05-10');

It raises an error. What is the cause?

SQL

SELECT EXTRACT(QUARTER FROM '2023-05-10');

A The date literal is missing a type cast to DATE or TIMESTAMP.

B The EXTRACT function requires the date to be in ISO format.

C QUARTER is not a valid field for EXTRACT in SQL.

D The FROM keyword is not allowed in EXTRACT.

💡 Hint

Check if the string literal is recognized as a date type by the database.

The string '2023-05-10' is a plain string literal. EXTRACT expects a date or timestamp type. Without casting, it causes a type error.