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().