YEAR, MONTH, DAY extraction in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Extracting the Year from a Date

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

SELECT YEAR(event_date) AS year_extracted FROM Events WHERE event_date = '2023-06-15';

SQL

CREATE TABLE Events (event_date DATE);
INSERT INTO Events VALUES ('2023-06-15');

A [{"year_extracted": "2023-06-15"}]

B [{"year_extracted": 15}]

C [{"year_extracted": 6}]

D [{"year_extracted": 2023}]

💡 Hint

The YEAR() function extracts the year part from a date.

The YEAR() function returns the year component as an integer from a date value. Here, it extracts 2023 from '2023-06-15'.

❓ query_result

intermediate

Extracting Month and Day from a Date

What will be the output of this query on the table Orders with a column order_date containing '2022-12-05' ?

SELECT MONTH(order_date) AS month_extracted, DAY(order_date) AS day_extracted FROM Orders WHERE order_date = '2022-12-05';

SQL

CREATE TABLE Orders (order_date DATE);
INSERT INTO Orders VALUES ('2022-12-05');

A [{"month_extracted": "12", "day_extracted": "05"}]

B [{"month_extracted": 5, "day_extracted": 12}]

C [{"month_extracted": 12, "day_extracted": 5}]

D [{"month_extracted": 2022, "day_extracted": 5}]

💡 Hint

MONTH() extracts the month number, DAY() extracts the day number.

MONTH('2022-12-05') returns 12 and DAY('2022-12-05') returns 5 as integers.

📝 Syntax

advanced

Identify the Syntax Error in Date Extraction

Which option contains a syntax error when trying to extract the year from a date column birth_date in table Users?

SQL

SELECT YEAR(birth_date) FROM Users;

A SELECT YEAR[birth_date] FROM Users;

B SELECT YEAR(birth_date) FROM Users;

C SELECT YEAR(birth_date) AS year FROM Users;

D SELECT EXTRACT(YEAR FROM birth_date) FROM Users;

💡 Hint

Square brackets are not used for function arguments in SQL.

Option A uses square brackets which is invalid syntax for function calls in SQL. Parentheses must be used.

❓ query_result

advanced

Using EXTRACT to Get Date Parts

What is the output of this query on table Appointments with appt_date = '2024-01-09' ?

SELECT EXTRACT(MONTH FROM appt_date) AS month_part, EXTRACT(DAY FROM appt_date) AS day_part FROM Appointments WHERE appt_date = '2024-01-09';

SQL

CREATE TABLE Appointments (appt_date DATE);
INSERT INTO Appointments VALUES ('2024-01-09');

A [{"month_part": "01", "day_part": "09"}]

B [{"month_part": 1, "day_part": 9}]

C [{"month_part": 9, "day_part": 1}]

D [{"month_part": 2024, "day_part": 9}]

💡 Hint

EXTRACT returns integer parts of the date.

EXTRACT(MONTH FROM '2024-01-09') returns 1 and EXTRACT(DAY FROM '2024-01-09') returns 9 as integers.

🧠 Conceptual

expert

Understanding Date Extraction Functions Across SQL Dialects

Which statement is correct about extracting year, month, and day from a date column in different SQL dialects?

A In MySQL, both YEAR(date_column) and EXTRACT(YEAR FROM date_column) return the year as an integer.

B EXTRACT(YEAR FROM date_column) works in PostgreSQL and standard SQL but not in MySQL.

C The function YEAR() is supported in all SQL dialects including PostgreSQL, MySQL, and SQL Server.

D SQL Server uses EXTRACT(YEAR FROM date_column) to get the year part.

💡 Hint

Check which functions are supported by each SQL dialect.

MySQL supports both YEAR() and EXTRACT() functions for date parts. PostgreSQL supports EXTRACT but not YEAR(). SQL Server uses DATEPART() instead of EXTRACT().

Practice

1. What does the SQL function EXTRACT(YEAR FROM order_date) return when used on a date column?

easy

A. The full date in order_date

B. The year part of the date in order_date

C. The day part of the date in order_date

D. The month part of the date in order_date

Solution

1. Step 1: Understand the EXTRACT function

   The EXTRACT function is used to get a specific part of a date, such as year, month, or day.

2. Step 2: Identify the part requested

   Here, EXTRACT(YEAR FROM order_date) extracts the year part from the date in the order_date column.

3. Final Answer:

   The year part of the date in order_date -> Option B

4. Quick Check:

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

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

Common Mistakes:

• Confusing YEAR with MONTH or DAY
• Expecting full date instead of part
• Using wrong syntax like EXTRACT(order_date YEAR)

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

easy

A. EXTRACT(MONTH FROM sale_date)

B. EXTRACT(MONTH sale_date)

C. EXTRACT(FROM sale_date MONTH)

D. EXTRACT(sale_date, MONTH)

Solution

1. Step 1: Recall the correct EXTRACT syntax

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

2. Step 2: Match syntax with options

   EXTRACT(MONTH FROM sale_date) uses EXTRACT(MONTH FROM sale_date), which matches the correct syntax.

3. Final Answer:

   EXTRACT(MONTH FROM sale_date) -> Option A

4. Quick Check:

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

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

Common Mistakes:

• Omitting FROM keyword
• Swapping order of arguments
• Using commas inside EXTRACT

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

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

medium

A. All month numbers of events that happened in December

B. Syntax error due to wrong EXTRACT usage

C. All full dates of events in December

D. All day numbers of events that happened in December

Solution

1. Step 1: Analyze the WHERE clause

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

2. Step 2: Analyze the SELECT clause

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

3. Final Answer:

   All day numbers of events that happened in December -> Option D

4. Quick Check:

   EXTRACT filters month=12, selects day numbers [OK]

Hint: Filter by month, select day with EXTRACT [OK]

Common Mistakes:

• Confusing day with month in SELECT
• Expecting full date instead of day number
• Thinking query has syntax error

4. Identify the error in this SQL query:

SELECT EXTRACT(YEAR sale_date) FROM sales;

medium

A. Missing FROM keyword inside EXTRACT

B. sale_date should be in quotes

C. EXTRACT cannot be used on date columns

D. No error, query is correct

Solution

1. Step 1: Check EXTRACT syntax

   EXTRACT requires the syntax EXTRACT(part FROM source). The keyword FROM is missing here.

2. Step 2: Confirm correct usage

   The correct query should be SELECT EXTRACT(YEAR FROM sale_date) FROM sales;

3. Final Answer:

   Missing FROM keyword inside EXTRACT -> Option A

4. Quick Check:

   EXTRACT(part FROM date) needs FROM [OK]

Hint: Always include FROM in EXTRACT syntax [OK]

Common Mistakes:

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

5. You want to find all orders placed on the 15th day of any month in 2023 from the orders table with a order_date column. Which query correctly filters these orders?

hard

A. SELECT * FROM orders WHERE EXTRACT(MONTH FROM order_date) = 15 AND EXTRACT(YEAR FROM order_date) = 2023;

B. SELECT * FROM orders WHERE DAY(order_date) = 15 AND YEAR(order_date) = 2023;

C. SELECT * FROM orders WHERE EXTRACT(DAY FROM order_date) = 15 AND EXTRACT(YEAR FROM order_date) = 2023;

D. SELECT * FROM orders WHERE EXTRACT(DAY FROM order_date) = 2023 AND EXTRACT(YEAR FROM order_date) = 15;

Solution

1. Step 1: Identify correct EXTRACT usage for day and year

   We want day = 15 and year = 2023, so EXTRACT(DAY FROM order_date) = 15 and EXTRACT(YEAR FROM order_date) = 2023.

2. Step 2: Check each option

   SELECT * FROM orders WHERE EXTRACT(DAY FROM order_date) = 15 AND EXTRACT(YEAR FROM order_date) = 2023; matches the correct conditions. SELECT * FROM orders WHERE DAY(order_date) = 15 AND YEAR(order_date) = 2023; uses non-standard functions that may not work in all SQL dialects. SELECT * FROM orders WHERE EXTRACT(MONTH FROM order_date) = 15 AND EXTRACT(YEAR FROM order_date) = 2023; incorrectly uses MONTH = 15. SELECT * FROM orders WHERE EXTRACT(DAY FROM order_date) = 2023 AND EXTRACT(YEAR FROM order_date) = 15; swaps day and year values incorrectly.

3. Final Answer:

   SELECT * FROM orders WHERE EXTRACT(DAY FROM order_date) = 15 AND EXTRACT(YEAR FROM order_date) = 2023; -> Option C

4. Quick Check:

   Filter day=15 and year=2023 with EXTRACT [OK]

Hint: Use EXTRACT(DAY FROM date) and EXTRACT(YEAR FROM date) together [OK]

Common Mistakes:

• Mixing up day and month
• Using non-standard functions without checking dialect
• Swapping day and year values