DATEDIFF and TIMESTAMPDIFF in MySQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to find the number of days between two dates.

MySQL

SELECT DATEDIFF([1], '2024-01-01') AS days_difference;

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

A'2024-01-10'

B'2023-12-31'

C'2024-02-01'

D'2024-01-01'

Attempts:

3 left

💡 Hint

Common Mistakes

Using the earlier date as the first argument results in a negative number.

Forgetting to put dates in quotes.

The DATEDIFF function returns the number of days between two dates. Here, the first date should be later than the second to get a positive difference.

2fill in blank

medium

Complete the code to find the number of months between two dates using TIMESTAMPDIFF.

MySQL

SELECT TIMESTAMPDIFF([1], '2023-01-01', '2024-01-01') AS months_difference;

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

ADAY

BYEAR

CMONTH

DHOUR

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'DAY' instead of 'MONTH' returns days, not months.

Using lowercase units which may cause errors.

TIMESTAMPDIFF calculates the difference between two dates in the specified unit. Here, MONTH returns the number of full months between the dates.

3fill in blank

hard

Fix the error in the code to correctly calculate the difference in hours between two timestamps.

MySQL

SELECT TIMESTAMPDIFF([1], '2024-01-01 08:00:00', '2024-01-01 12:00:00') AS hours_difference;

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

ADAY

BMINUTE

CSECOND

DHOUR

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'MINUTE' returns minutes, not hours.

Using 'DAY' returns days, which is too large for this difference.

The unit HOUR correctly calculates the difference in hours between two timestamps.

4fill in blank

hard

Fill both blanks to calculate the number of years and days between two dates.

MySQL

SELECT TIMESTAMPDIFF([1], '2000-01-01', '2024-01-01') AS years_diff, DATEDIFF('2024-01-01', [2]) AS days_diff;

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

AYEAR

B'2000-01-01'

C'2023-12-31'

DMONTH

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'MONTH' instead of 'YEAR' for years difference.

Swapping dates in DATEDIFF causing negative results.

The first blank uses YEAR to get full years difference. The second blank uses the start date '2000-01-01' to calculate days difference from the end date.

5fill in blank

hard

Fill all three blanks to calculate the difference in days, months, and years between two dates.

MySQL

SELECT DATEDIFF([1], [2]) AS days_diff, TIMESTAMPDIFF([3], [2], [1]) AS months_diff, TIMESTAMPDIFF(YEAR, [2], [1]) AS years_diff;

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

A'2024-12-31'

B'2023-01-01'

CMONTH

DDAY

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping dates causing negative differences.

Using wrong units like 'DAY' for months difference.

The first two blanks are dates: the later date first, then the earlier date. The third blank is the unit 'MONTH' for months difference.