DATE_FORMAT and EXTRACT in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to format the date as 'Year-Month-Day'.

SQL

SELECT DATE_FORMAT(order_date, '[1]') AS formatted_date FROM orders;

Drag options to blanks, or click blank then click option'

A'%Y-%m-%d'

B'%d/%m/%Y'

C'%m-%d-%Y'

D'%H:%i:%s'

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong order of year, month, and day.

Using slashes instead of dashes.

Using time format codes instead of date.

The format '%Y-%m-%d' outputs the date as Year-Month-Day, which matches the requirement.

2fill in blank

medium

Complete the code to extract the month from the date.

SQL

SELECT EXTRACT([1] FROM order_date) AS order_month FROM orders;

Drag options to blanks, or click blank then click option'

AHOUR

BDAY

CYEAR

DMONTH

Attempts:

3 left

💡 Hint

Common Mistakes

Using DAY instead of MONTH.

Using YEAR when month is needed.

Using HOUR which is for time, not date.

EXTRACT(MONTH FROM date) returns the month part of the date.

3fill in blank

hard

Fix the error in the code to extract the year from the date.

SQL

SELECT EXTRACT(YEAR [1] order_date) AS order_year FROM orders;

Drag options to blanks, or click blank then click option'

AIN

BBY

CFROM

DON

Attempts:

3 left

💡 Hint

Common Mistakes

Omitting the FROM keyword.

Using BY, IN, or ON instead of FROM.

The correct syntax is EXTRACT(YEAR FROM date), so 'FROM' is required.

4fill in blank

hard

Fill both blanks to format the date as 'Month/Day/Year' and extract the day.

SQL

SELECT DATE_FORMAT(order_date, '[1]') AS formatted_date, EXTRACT([2] FROM order_date) AS day_part FROM orders;

Drag options to blanks, or click blank then click option'

A'%m/%d/%Y'

B'%Y-%m-%d'

CDAY

DMONTH

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong date format codes.

Extracting MONTH instead of DAY.

The format '%m/%d/%Y' gives Month/Day/Year, and EXTRACT(DAY FROM date) extracts the day.

5fill in blank

hard

Fill all three blanks to format the date as 'Year.Month.Day', extract the year, and extract the month.

SQL

SELECT DATE_FORMAT(order_date, '[1]') AS formatted_date, EXTRACT([2] FROM order_date) AS year_part, EXTRACT([3] FROM order_date) AS month_part FROM orders;

Drag options to blanks, or click blank then click option'

A'%d-%m-%Y'

BYEAR

CMONTH

D'%Y.%m.%d'

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong date format string.

Mixing up YEAR and MONTH in EXTRACT.

The format '%Y.%m.%d' formats date as Year.Month.Day, EXTRACT(YEAR FROM date) extracts year, EXTRACT(MONTH FROM date) extracts month.

Practice

1. What does the SQL function EXTRACT(MONTH FROM '2024-06-15') return?

easy

A. '2024-06-15'

B. 15

C. 2024

D. 6

Solution

1. Step 1: Understand EXTRACT function

   The EXTRACT function pulls out a specific part of a date, here the MONTH part.

2. Step 2: Apply EXTRACT to the date

   From the date '2024-06-15', the month is 6 (June).

3. Final Answer:

   6 -> Option D

4. Quick Check:

   EXTRACT(MONTH FROM date) = 6 [OK]

Hint: EXTRACT pulls one date part, like month or year [OK]

Common Mistakes:

• Confusing day with month
• Expecting full date instead of part
• Using wrong syntax for EXTRACT

2. Which of the following is the correct syntax to get the year from a date using EXTRACT in SQL?

easy

A. EXTRACT(YEAR FROM '2024-06-15')

B. EXTRACT YEAR FROM '2024-06-15'

C. EXTRACT('YEAR', '2024-06-15')

D. EXTRACT('2024-06-15' FROM YEAR)

Solution

1. Step 1: Recall EXTRACT syntax

   The correct syntax is EXTRACT(part FROM date), with parentheses and FROM keyword.

2. Step 2: Check each option

   EXTRACT(YEAR FROM '2024-06-15') matches the correct syntax: EXTRACT(YEAR FROM '2024-06-15'). Others miss parentheses or have wrong order.

3. Final Answer:

   EXTRACT(YEAR FROM '2024-06-15') -> Option A

4. Quick Check:

   Correct EXTRACT syntax uses parentheses and FROM [OK]

Hint: EXTRACT needs parentheses and FROM keyword [OK]

Common Mistakes:

• Omitting parentheses
• Swapping arguments order
• Using quotes around part name

3. What is the output of this SQL query?

SELECT DATE_FORMAT('2024-06-15', '%Y-%m-%d'), EXTRACT(DAY FROM '2024-06-15');

medium

A. 2024-06-15, 6

B. 2024-06-15, 15

C. 2024/06/15, 15

D. 06-15-2024, 15

Solution

1. Step 1: Understand DATE_FORMAT with '%Y-%m-%d'

   This format outputs year-month-day as '2024-06-15'.

2. Step 2: Understand EXTRACT(DAY FROM date)

   Extracting DAY from '2024-06-15' gives 15.

3. Final Answer:

   2024-06-15, 15 -> Option B

4. Quick Check:

   DATE_FORMAT + EXTRACT day = '2024-06-15', 15 [OK]

Hint: DATE_FORMAT '%Y-%m-%d' matches original date format [OK]

Common Mistakes:

• Mixing month and day order
• Wrong format string in DATE_FORMAT
• Extracting wrong date part

4. Identify the error in this SQL query:

SELECT DATE_FORMAT('2024-06-15', '%Y-%d-%m');

medium

A. The date string must be in MM-DD-YYYY format

B. DATE_FORMAT requires three arguments

C. The format string swaps day and month incorrectly

D. EXTRACT should be used instead of DATE_FORMAT

Solution

1. Step 1: Analyze the format string '%Y-%d-%m'

   This format prints year, then day, then month, which is unusual and likely a mistake.

2. Step 2: Understand common date format order

   Usually, month comes before day as '%Y-%m-%d'. Swapping day and month causes confusing output.

3. Final Answer:

   The format string swaps day and month incorrectly -> Option C

4. Quick Check:

   Correct order is year-month-day, not year-day-month [OK]

Hint: Check format order: month before day usually [OK]

Common Mistakes:

• Mixing day and month in format string
• Wrong number of arguments to DATE_FORMAT
• Confusing DATE_FORMAT with EXTRACT

5. You want to list all orders from a table orders showing only the year and month of the order date in format YYYY-MM. Which SQL query correctly does this?

hard

A. SELECT DATE_FORMAT(order_date, '%Y-%m') AS year_month FROM orders;

B. SELECT EXTRACT(YEAR_MONTH FROM order_date) AS year_month FROM orders;

C. SELECT DATE_FORMAT(order_date, '%Y-%d') AS year_month FROM orders;

D. SELECT EXTRACT(MONTH FROM order_date) AS year_month FROM orders;

Solution

1. Step 1: Understand DATE_FORMAT for year and month

   Using '%Y-%m' in DATE_FORMAT returns year and month as 'YYYY-MM'.

2. Step 2: Check other options

   SELECT EXTRACT(YEAR_MONTH FROM order_date) AS year_month FROM orders; is invalid syntax; EXTRACT does not support YEAR_MONTH. SELECT DATE_FORMAT(order_date, '%Y-%d') AS year_month FROM orders; uses day instead of month. SELECT EXTRACT(MONTH FROM order_date) AS year_month FROM orders; returns only month number, missing year.

3. Final Answer:

   SELECT DATE_FORMAT(order_date, '%Y-%m') AS year_month FROM orders; -> Option A

4. Quick Check:

   DATE_FORMAT with '%Y-%m' gives year-month [OK]

Hint: Use DATE_FORMAT with '%Y-%m' for year and month [OK]

Common Mistakes:

• Using EXTRACT with unsupported parts
• Mixing day and month in format
• Selecting only month without year