Chapter 4 review (Section 4.11): built-in SQL functions

Section 4.11a

Date and Time Functions

Dates are stored as dates, not text, precisely so the database can do arithmetic and comparisons on them directly. Date functions are how a query gets at the pieces of a date, or moves a date forward and back.

Two invoices dated 2026-03-14 and 2026-03-01 are not just different strings; the database knows one is 13 days after the other, and date functions are what let a query act on that fact.

Extracting a piece of a date

YEAR(), MONTH(), and DAY() pull one component out of a date value, returning a plain number you can filter or group on:

SELECT INV_NUM, INV_DATE
FROM   INVOICE
WHERE  YEAR(INV_DATE) = 2026;
-- every invoice billed during 2026, regardless of month or day

SELECT MONTH(INV_DATE) AS BILLING_MONTH, COUNT(*) AS INVOICE_COUNT
FROM   INVOICE
GROUP BY MONTH(INV_DATE)
ORDER BY BILLING_MONTH;
-- how many invoices were written in each calendar month

Getting today's date

CURDATE() (MySQL) or CURRENT_DATE (standard SQL) returns the date the query is actually running, useful for anything relative to "now":

SELECT INV_NUM, INV_DATE
FROM   INVOICE
WHERE  INV_DATE = CURDATE();
-- invoices written today

Date arithmetic: adding and subtracting

Because a date is stored as a real date value, adding a number of days to it produces another valid date. This is exactly how a due date gets computed from an invoice date:

SELECT INV_NUM, INV_DATE,
       DATE_ADD(INV_DATE, INTERVAL 30 DAY) AS DUE_DATE
FROM   INVOICE;
-- every invoice's payment due date, 30 days after it was written

DATEDIFF() goes the other direction, subtracting two dates to get the number of days between them:

SELECT INV_NUM, INV_DATE,
       DATEDIFF(CURDATE(), INV_DATE) AS DAYS_OLD
FROM   INVOICE
WHERE  DATEDIFF(CURDATE(), INV_DATE) > 60;
-- every invoice more than 60 days old, with its exact age in days

Section 4.11b

Numeric Functions

Numeric functions round, truncate, and reshape numbers, the difference between a price that displays cleanly and one that shows nine decimal places by accident.

ROUND versus TRUNCATE

These two are confused constantly because they look similar and usually agree. They disagree exactly at the digit being cut: ROUND looks at the next digit and rounds up when appropriate; TRUNCATE never rounds, it simply drops everything past the cutoff.

SELECT P_PRICE,
       ROUND(P_PRICE, 1)    AS ROUNDED,
       TRUNCATE(P_PRICE, 1) AS TRUNCATED
FROM   PRODUCT;
-- a price of 14.97 rounds to 15.0 but truncates to 14.9

A worked example: computing a discounted price

A 15 percent discount, cleanly rounded to two decimal places for display, needs both a computed expression and ROUND wrapped around it:

SELECT P_CODE, P_PRICE,
       ROUND(P_PRICE * 0.85, 2) AS SALE_PRICE
FROM   PRODUCT;
-- every product's price after a 15% discount, rounded to the cent

MOD for grouping into buckets

MOD is the function version of the % operator, and it shows up constantly for splitting rows into even and odd, or into a fixed number of buckets:

SELECT P_CODE, P_QOH,
       CASE WHEN MOD(P_QOH, 2) = 0 THEN 'Even count' ELSE 'Odd count' END AS PARITY
FROM   PRODUCT;
-- labels each product's quantity on hand as even or odd

Section 4.11c

String Functions

String functions clean up, search inside, and rebuild text values, the tools behind almost every "why doesn't this match" problem in a WHERE clause.

Building a full name

CONCAT is how two separate columns, kept separate for good reason (sorting by last name, for instance), get combined only at display time:

SELECT CONCAT(CUS_FNAME, ' ', CUS_LNAME) AS FULL_NAME
FROM   CUSTOMER
ORDER BY CUS_LNAME;
-- "Maria Alvarez", "James Chen", ... sorted by the LNAME column even though it never appears alone in the output

Case-insensitive searching

A search for 'smith' will miss a row stored as 'Smith' unless both sides of the comparison are forced to the same case first:

SELECT CUS_LNAME, CUS_FNAME
FROM   CUSTOMER
WHERE  UPPER(CUS_LNAME) = UPPER('smith');
-- matches 'Smith', 'SMITH', and 'smith' alike

Pulling out part of a string

SUBSTRING extracts a piece of a longer string by position, useful whenever a code packs more than one piece of information into a single column:

SELECT P_CODE, SUBSTRING(P_CODE, 1, 2) AS CATEGORY_PREFIX
FROM   PRODUCT;
-- if product codes are built like 'AC-01452', this pulls out 'AC', the category prefix

Shortening a long description for a report

Combining SUBSTRING, LENGTH, and CONCAT is how a query truncates a long text field and marks that it was cut off, without ever risking an error on a description that was already short:

SELECT P_DESCRIPT,
       CASE WHEN LENGTH(P_DESCRIPT) > 20
            THEN CONCAT(SUBSTRING(P_DESCRIPT, 1, 20), '...')
            ELSE P_DESCRIPT
       END AS SHORT_DESCRIPT
FROM   PRODUCT;
-- descriptions over 20 characters are cut to 20 and marked with an ellipsis; shorter ones pass through untouched

Section 4.11d

Conversion Functions

Every value in SQL has a data type, and conversion functions are how a query moves a value from one type to another on purpose, instead of hoping the database guesses correctly.

Some type changes happen automatically; SQL will often convert a number to text to concatenate it without being asked. That automatic behavior is called implicit conversion. Explicit conversion, using a function like CAST, states the intended type directly and fails loudly, at the query, if the conversion is not actually possible, rather than silently producing a wrong answer somewhere downstream.

CAST: the standard, portable form

CAST(expression AS type) works across every major DBMS and should be the default choice when a conversion is genuinely needed:

SELECT P_CODE, CAST(P_PRICE AS INTEGER) AS WHOLE_DOLLAR_PRICE
FROM   PRODUCT;
-- 14.97 becomes 14: CAST to an integer truncates, it does not round

SELECT CONCAT('Invoice total: $', CAST(INV_TOTAL AS CHAR)) AS SUMMARY_LINE
FROM   INVOICE;
-- a numeric column explicitly converted to text before being joined with a literal string

Converting text that looks like a number

Data entered as text, for example from a form or an import file, often needs to become a real number before it can be used in arithmetic:

SELECT CAST('1250.50' AS DECIMAL(10,2)) + 100;
-- 1350.50 -- the text '1250.50' is converted to a real decimal number first, then added to 100

Converting a date to text for display

A stored date and a nicely formatted date for a report are two different jobs. CAST handles the type change; a dedicated formatting function, named differently per DBMS as the table in Section 4.11a showed, handles the exact display format:

SELECT INV_NUM, CAST(INV_DATE AS CHAR) AS INV_DATE_TEXT
FROM   INVOICE;
-- the date value becomes plain text, ready to concatenate into a longer message

CAST versus dialect-specific conversion functions

4.11 at a glance

One table, every function from this page, for whenever you just need the name back.

Practice: the movie rental database

Ten problems, one small SQLite database, no installation beyond Python itself. Every problem needs at least one function from this page to answer.

The practice notebook uses a three table movie rental database: MOVIE (10 rows), CUSTOMER (8 rows), and RENTAL (20 rows). On purpose, RENTAL only stores a rental_date and a return_date, nothing else; a due date, a late fee, and "days rented" all have to be computed with the functions from Sections 4.11a–4.11d, which is exactly the point.

connection, cursor = create_database()

Once you have given every problem a real attempt, download the solutions notebook to check your work. It is a single self-contained file too, the same ten problems with the SQL filled in and the actual output already shown, so you can read it without needing to run anything first.

Prefer to build the database in your own scripts outside a notebook? The same setup code is also available on its own as movie_rental_db.py, importable the usual way once it's saved next to your script.