Level 6: Working with text and dates

Lesson

SQL can transform text. UPPER and LOWER put text in uppercase or lowercase, which helps clean up badly typed data such as “bob durand” or “CLAIRE ROUX”. LENGTH gives the number of characters.

SUBSTR(text, start, length) extracts a piece of text. The first character is at position 1, not 0: SUBSTR('Paris', 1, 3) gives 'Par'.

The || operator sticks pieces of text together: name || ' (' || city || ')' gives “Lena (Lyon)”. Careful: if one of the joined values is NULL, the whole result becomes NULL.

In SpeedQL (SQLite), a date is text in the 'YYYY-MM-DD' format. This format has a big advantage: alphabetical order is also chronological order. So you compare dates directly: '2025-03-01' < '2025-04-15'.

To extract part of a date, you use strftime(format, date). '%Y' gives the year, '%m' the month, '%d' the day, '%Y-%m' the year and month. strftime('%Y', joined) gives for example '2022'. The result is text: you compare it with '2022', between quotes.

To shift a date, you use date(date, shift): date('2025-07-01', '+1 day') gives '2025-07-02'. Shifts are written '+7 days', '-1 month' or '+1 year'. strftime accepts the same shifts after the date.

To measure a gap, julianday(date) converts a date into a number of days. The difference between two julianday values therefore gives a gap in days: julianday(return_date) - julianday(loan_date) gives the length of a loan.

Finally, CAST(value AS type) converts a value from one type to another: CAST(strftime('%Y', joined) AS INTEGER) gives the number 2022 instead of the text '2022', which you can then use in calculations (seniority, for example).

Syntax

SELECT UPPER(name),
       name || ' - ' || city,
       SUBSTR(text, 1, 4),
       strftime('%Y', date_col),
       date(date_col, '+7 days')
FROM my_table;

Worked example

SELECT name || ' (' || city || ')' AS label, strftime('%Y', joined) AS join_year
FROM members;

We build a readable label by gluing the name and the city, and we extract the sign-up year.

Example result

Key points

• || concatenates text.
• SUBSTR counts from 1.
• strftime extracts part of a date, julianday lets you compute gaps.

Common pitfalls

• strftime returns text: compare with '2023', between quotes.
• A date with a time is “greater” than the date alone: '2025-06-05 10:00' > '2025-06-05'. departs BETWEEN '2025-06-01' AND '2025-06-05' therefore misses the flights of 5 June. Write departs < '2025-06-06' as the end bound.
• If one of the values joined with || is NULL, the whole result becomes NULL.

SQLite specifics

SQLite has no real date type: a date is text in the 'YYYY-MM-DD' format, and strftime, date and julianday are specific to SQLite. Other engines have a DATE type and other functions (EXTRACT, DATE_TRUNC, DATEDIFF…).

Going further

TRIM(text) removes the spaces at the start and the end; REPLACE(text, '-', '/') replaces every occurrence; INSTR(text, '@') gives the position of a character (0 if it is absent). Combined: SUBSTR(email, INSTR(email, '@') + 1) extracts the domain of an address ('mail.com').

The level’s 5 exercises

1. Guided · Show the name of each contact in uppercase (name_upper) and its number of characters (name_length). (Table: contacts)
2. Practice · For each hotel guest, show a column guest in the format “name - country” (for example “Ana Silva - Portugal”). (Table: guests)
3. Practice · Show the airline and departure time of each flight (for example 08:10) in a column named hour. (Table: flights)
4. Practice · Show the name of the members who joined in 2023. (Table: members)
5. Challenge · A loan lasts at most 21 days. For the loans returned late, show the id, the return deadline (due_date), the return date and the number of days late (days_late). (Table: loans)

In SpeedQL, every query is checked straight away: its result is compared with the expected one, then the query is run again on a hidden control database. Each exercise has written hints, to show only if you get stuck.

See also: the cheat sheet card · the SQL exercises with solutions on this topic