The number and the date functions are the arithmetic layer of the SQL. They round, they scale, they measure the distance between two values, and they pull the pieces out of a calendar. Because the dates are stored as the text in this archive, the date half of the lesson matters the most. The same string can be compared, sorted, sliced or measured only once you decide which function should read it.
The previous lesson in this module covered "How Do You Clean Messy Text Data With SQL?", and the next lesson in this module is about "How Do CAST and COALESCE Fix Messy Data?".
What do the number and the date functions actually compute?
Every function in this family takes the values in and it hands back a single value, per row. That is the whole contract, and it is why they compose. The result of one function is a legal argument to the next.
The archive's duration column stores the minutes as a REAL, so it can be scaled directly.
SELECT title, duration,
ROUND(duration / 15.0, 2) AS quarter_hours,
ROUND(duration / 15.0) AS whole_quarter_hours
FROM episodes
ORDER BY id
LIMIT 6;title duration quarter_hours whole_quarter_hours
----------------------------------- -------- -------------- --------------------
Mapping the shore with LiDAR 47.0 3.13 3
The boundary of a map 38.0 2.53 3
Street photography at night 52.0 3.47 3
Developing film in a hotel bathroom 61.0 4.07 4
Composing with a single lens 44.0 2.93 3
Why the horizon curves 29.0 1.93 2There are two things to read here. The 1.0 promotion is what keeps the 47 divided by 15 from truncating to the 3 before the ROUND ever sees it, and that is the same integer-division trap that lesson 11 opened with. And the second column shows what the ROUND does with no digit argument. It rounds to the zero decimal places, so the 2.53 and the 2.93 both become 3. The number of the digits is where you make the precision decision.
What do the ABS, the modulo and the ceiling blocks measure?
Three more numbers and three different questions. The ABS answers "how far from the target", and it ignores the direction. That is useful when a deviation upward and a deviation downward are equally bad.
SELECT title, downloads, ABS(downloads - 25000) AS gap_from_target
FROM episodes
ORDER BY gap_from_target
LIMIT 4;title downloads gap_from_target
----------------------------- --------- ---------------
Composing with a single lens 25360 360
The photograph nobody printed 21800 3200
Mapping the shore with LiDAR 28410 3410
The boundary of a map 19205 5795The modulo operator % answers "what is left over", which makes it a bucketing tool. It gives you the last three digits of a download count, or a parity check on a row number. The CEIL answers "how many whole units do I need", and it always rounds up. That is how you price the capacity that cannot be fractionally consumed.
SELECT title, downloads,
downloads % 1000 AS last_three,
CEIL(downloads / 500.0) AS blocks_of_500
FROM episodes
ORDER BY id
LIMIT 6;title downloads last_three blocks_of_500
----------------------------------- --------- -------------- -------------
Mapping the shore with LiDAR 28410 410 57.0
The boundary of a map 19205 205 39.0
Street photography at night 33417 417 67.0
Developing film in a hotel bathroom 41203 203 83.0
Composing with a single lens 25360 360 51.0
Why the horizon curves 17882 882 36.0Notice that the CEIL rounds up while the FLOOR rounds down, and the difference matters whenever the unit is a physical object. The eleven episodes need the twelve seats, and the nine episodes need the nine.
Pairing the two with a scale-changing function gives you a compact view of the same downloads figure from the two directions.
SELECT title,
FLOOR(downloads / 1000.0) AS whole_thousands,
CEIL(downloads / 1000.0) AS rounded_up,
ROUND(SQRT(downloads), 2) AS concentration_index
FROM episodes
ORDER BY id
LIMIT 6;title whole_thousands rounded_up concentration_index
----------------------------------- --------------- ---------- --------------------
Mapping the shore with LiDAR 28.0 29.0 168.55
The boundary of a map 19.0 20.0 138.58
Street photography at night 33.0 34.0 182.8
Developing film in a hotel bathroom 41.0 42.0 202.99
Composing with a single lens 25.0 26.0 159.25
Why the horizon curves 17.0 18.0 133.72The square root is the odd one out, and it earns its place by changing the shape of the scale rather than the precision. Taking the root of a download count compresses the long tail, so one viral episode cannot dominate an average that is built on this column. Many of the analytics teams call this a root-scaled index.
The exact function is interchangeable, because the POW(downloads, 0.5) computes the same value. The choice of the scale belongs to the question that is being asked, and not to the column.
How do you read the pieces out of a date?
In this archive the released is an ISO string, which is the YYYY-MM-DD shape. That layout is deliberately friendly. It sorts chronologically with the plain text ordering, as lesson 10 showed, and it has the fixed-width parts, so a year is the first four characters and a month is the characters six and seven.
SQLite exposes that through the strftime, whose format string says which part you want.
SELECT id, released,
strftime('%Y', released) AS yr,
strftime('%m', released) AS mon,
strftime('%w', released) AS dow_num,
CASE strftime('%w', released)
WHEN '0' THEN 'Sunday'
WHEN '1' THEN 'Monday'
WHEN '2' THEN 'Tuesday'
WHEN '3' THEN 'Wednesday'
WHEN '4' THEN 'Thursday'
WHEN '5' THEN 'Friday'
WHEN '6' THEN 'Saturday'
END AS dow_name
FROM episodes
ORDER BY id
LIMIT 6;id released yr mon dow_num dow_name
-- ---------- ---- --- ------- ---------
1 2025-03-04 2025 03 2 Tuesday
2 2025-03-11 2025 03 2 Tuesday
3 2025-01-08 2025 01 3 Wednesday
4 2025-01-15 2025 01 3 Wednesday
5 2025-02-05 2025 02 3 Wednesday
6 2025-03-18 2025 03 2 TuesdayThe %w returns the weekday as a number where the 0 is Sunday. That is a convention rather than a natural ordering, because the Tuesday sorts before the Wednesday, but the Saturday sorts before the Sunday. The CASE turns the number into a name, and it is worth noticing that the strftime of SQLite has no %A specifier for a full weekday name, so the CASE is not optional here.
The same trick produces the month key that every later grouping will use.
SELECT id, released, strftime('%Y-%m', released) AS month_key
FROM episodes
ORDER BY released
LIMIT 6;id released month_key
-- ---------- ---------
7 2024-11-21 2024-11
3 2025-01-08 2025-01
4 2025-01-15 2025-01
5 2025-02-05 2025-02
1 2025-03-04 2025-03
2 2025-03-11 2025-03The month key sorts correctly as the text because the year and the month are both zero-padded, and it groups correctly because every release in a month shares the same seven characters. If the dates were stored day-first, the same expression would produce the nonsense, and nothing would warn you. That is why the format is a schema decision and not a data accident.
How do you measure and shift a date?
SQLite stores the dates as the text, but it computes with them through the julianday, which converts a date into a fractional day number. Subtracting two of them gives you an elapsed interval in days. The result is a REAL, so you wrap it in the CAST(... AS INTEGER) when you want the whole days.
SELECT id, released,
CAST(julianday('2026-01-01') - julianday(released) AS INTEGER) AS days_old
FROM episodes
ORDER BY released
LIMIT 6;id released days_old
-- ---------- --------
7 2024-11-21 406
3 2025-01-08 358
4 2025-01-15 351
5 2025-02-05 330
1 2025-03-04 303
2 2025-03-11 296There are two choices in that query that are deliberate. The reference date is a fixed literal rather than a 'now', because a query that returns the different numbers every time that you run it cannot be regression-tested. And the whole days come from an explicit CAST, since the subtraction of the two Julian days lands on a fractional value when the input strings carry a time component.
Shifting a date forward is the same machinery with a modifier appended. The call date(released, '+30 days') walks forward, and the named modifiers can also snap to a calendar boundary.
SELECT id, released,
date(released, '+30 days') AS promo_date,
date(released, 'start of month') AS month_start
FROM episodes
ORDER BY id
LIMIT 6;id released promo_date month_start
-- ---------- ----------- -----------
1 2025-03-04 2025-04-03 2025-03-01
2 2025-03-11 2025-04-10 2025-03-01
3 2025-01-08 2025-02-07 2025-01-01
4 2025-01-15 2025-02-14 2025-01-01
5 2025-02-05 2025-03-07 2025-02-01
6 2025-03-18 2025-04-17 2025-03-01Every date function here is spelled the SQLite way. The other engines name the same ideas differently. PostgreSQL uses the EXTRACT(YEAR FROM released) or the date_part('year', released) and the date_trunc('month', released). MySQL uses the YEAR(released) and the DATE_SUB(released, INTERVAL 30 DAY). The SQL Server uses the DATEPART(month, released) and the DATEADD(day, 30, released). Oracle uses the ADD_MONTHS(released, 1).
The portable habits survive the rename. You store the dates as the ISO text or as a real date type, you derive the parts explicitly, and you never hand-calculate a date difference in the application code.
Quick reference
- The ROUND takes an optional digit count, and the digit count is a business decision about the precision and not a technicality.
- The ABS measures the distance without the direction, the % returns the remainder, the CEIL rounds up and the FLOOR rounds down.
- The strftime extracts the parts of an ISO date, and the %w gives a weekday number that starts at Sunday.
- The julianday differences measure the elapsed days, and the CAST to INTEGER removes the fraction.
- You prefer a fixed reference date over a 'now' so that the results are reproducible and testable.
Measurement drill: using only the episodes table, you write a query that reports, for every episode, its age in days at a fixed reference date, the weekday that it was released on, and how many whole 15-minute blocks it fills. Then you explain in one sentence why each of the three needs a different family of the functions.