Sql - Dates - Comparing and Sorting Dates - Lesson

Sql · Dates

Comparing and Sorting Dates

The full text of this chapter, to read or revisit. To practise, do the exercises.

▶ Do the exercises

Exercise 1 — SQL Date Format: YYYY-MM-DD

In SQL, dates are typically stored and displayed in the format

YYYY-MM-DD

, which follows the ISO 8601 standard: -

YYYY

- Four-digit year (e.g., 2023) -

- Two-digit month (01-12) -

- Two-digit day (01-31) This format ensures dates are sorted chronologically when ordered alphabetically.

In our Codémon database, the

captured_date

column stores when each Codémon was captured. As you can see, the dates are in YYYY-MM-DD format, making them easy to compare and sort.

Important point about date format

The tricky part about dates in SQL is that the oldest date is the earliest one (it's the one that happened first in time). So, when you sort dates in ascending order, the oldest date comes first. For example, '2023-01-01' is earlier than '2023-12-31'.

Exercise 2 — Filtering Records by Date

To filter records based on dates, we use the

WHERE

clause with comparison operators. Date literals in SQL queries should be enclosed in single quotes:

'YYYY-MM-DD'

Common date comparisons include: -

Equal to (exactly this date) -

Before (earlier than) -

After (later than) -

On or before this date -

On or after this date

Here is an example SQL query to find Codémons captured on a specific date:

SELECT name, type, captured_date FROM codemon WHERE captured_date = '2023-03-15';

Exercise 3 — Date Comparison in SQL

Which of the following dates would be considered the "oldest" in SQL?

Options

• 2023-02-28

• 2023-03-15

• 2023-04-05

• 2023-05-10

Hint : Remember, in SQL, we think of dates like a timeline. The oldest date is the one that happened first in time.

Exercise 4 — Date Filtering Practice

Complete the SQL query to find all Codémons captured before April 1, 2023.

SELECT name, type, captured_date FROM codemon WHERE captured_date ____ ____;

Options: < > = '2023-04-01' '04-01-2023' '01-04-2023'

Hint : To filter for dates before a specific date, you need the correct comparison operator and the properly formatted date literal.

Exercise 5 — Sorting Records by Date

The

ORDER BY

clause can be used to sort records by date in either ascending (oldest to newest) or descending (newest to oldest) order.

ORDER BY captured_date ASC

- Sort from earliest date to latest date (oldest first) -

ORDER BY captured_date DESC

- Sort from latest date to earliest date (newest first)

SELECT name, type, captured_date FROM codemon ORDER BY captured_date ASC;

Default Sort Order

If you omit ASC or DESC, SQL defaults to ASC (ascending order). For dates, this means oldest dates will appear first.

Exercise 6 — Finding the Most Recent Capture

Which SQL query would find the most recently captured Codémon (the newest capture date)?

Options

• ```SELECT name, captured_date FROM codemon ORDER BY captured_date DESC LIMIT 1;```

• ```SELECT name, captured_date FROM codemon ORDER BY captured_date ASC LIMIT 1;```

• ```SELECT name, captured_date FROM codemon WHERE captured_date > '2025-01-01';```

• ```SELECT name, captured_date FROM codemon WHERE captured_date = NEWEST();```

Hint : To find the most recent (newest) date, think about which sorting direction would put the newest date first in the results.

Exercise 7 — Combining Date Filtering and Sorting

Complete the SQL query to find all Codémons captured in the first quarter of 2023 (January to March), sorted from newest to oldest.

SELECT name, type, captured_date FROM codemon WHERE captured_date ____ '2023-01-01' AND captured_date ____ '2023-03-31' ORDER BY captured_date ____;

Options: >= <= = > < ASC DESC

Hint : To find dates in a specific range, you need the correct comparison operators for the start and end dates, plus the right sorting direction.

Exercise 8 — Using BETWEEN for Date Ranges

The

BETWEEN

operator provides a convenient way to filter records within a range of dates. It's inclusive, meaning it includes both the start and end dates in the results.

The syntax is:

WHERE date_column BETWEEN 'start_date' AND 'end_date'

SELECT name, type, captured_date FROM codemon WHERE captured_date BETWEEN '2023-03-01' AND '2023-04-30' ORDER BY captured_date;

This is equivalent to:

WHERE captured_date >= '2023-03-01' AND captured_date <= '2023-04-30'

Exercise 9 — Date Comparison Operators

Match each date comparison operator with its correct description.

Items to match

• captured_date = '2023-03-15'

• captured_date > '2023-03-15'

• captured_date < '2023-03-15'

• captured_date BETWEEN '2023-03-01' AND '2023-05-31'

Definitions

• Exactly on March 15, 2023

• After March 15, 2023

• Before March 15, 2023

• During March through May 2023 (inclusive)

Exercise 10 — Using Pattern Matching with Dates

You can use pattern matching with the

operator to filter dates by year, month, or other patterns. This is particularly useful when you want to find all records from a specific month or year.

For example, to find all records from March 2023:

SELECT name, type, captured_date FROM codemon WHERE captured_date LIKE '2023-03%' ORDER BY captured_date;

The

is a wildcard that matches any sequence of characters. So

'2023-03%'

matches any date that starts with '2023-03'.

Common Date Patterns

LIKE '2023%'

- All dates in 2023 -

LIKE '%-03-%'

- All dates in March (any year) -

LIKE '2023-%-15'

- All dates on the 15th day of any month in 2023

Exercise 11 — Date Pattern Matching Practice

Complete the SQL query to find all Codémons captured in May of any year using pattern matching.

SELECT name, type, captured_date FROM codemon WHERE captured_date ____ ____ ORDER BY captured_date;

Options: LIKE = '%-05-%' '%05%' '2023-05%' BETWEEN

Hint : To match a specific month in a date string, you need to use the right pattern with the % wildcard characters in the right places.

Exercise 12 — Complex Date Query

Which SQL query would find all Codémons with power greater than 75 captured before April 2023, sorted by capture date?

Options

• ```SELECT name, type, power, captured_date FROM codemon WHERE power > 75 AND captured_date < '2023-04-01' ORDER BY captured_date;```

• ```SELECT name, type, power, captured_date FROM codemon WHERE power > 75 OR captured_date < '2023-04-01' ORDER BY captured_date;```

• ```SELECT name, type, power, captured_date FROM codemon WHERE power > 75 ORDER BY captured_date < '2023-04-01';```

• ```SELECT name, type, power, captured_date FROM codemon WHERE power > 75 AND date < '2023-04-01' ORDER BY captured_date;```

Hint : To solve this, you need to combine filtering by power, filtering by date, and sorting correctly.

← Previous lesson : Quizz Next lesson : Quizz →

Back to the course