Dates and Text: Functions and Where Databases Differ
Work with dates and text using functions for differences, date arithmetic, case, slices and joining, and learn where each database uses its own names.
In this lesson you will
- Calculate with dates
- Change and measure text
- Join text with concatenation
- Know where database functions differ
About 28 minutes
Dates and words in the data
Maya’s reports rarely stop at numbers. She wants to know how long a customer waited between joining and first buying, and which date a payment is due. She wants product names in capitals for labels, and a short code made from a name. These jobs use date functions and text functions. They are also the area where databases differ the most. The ideas travel well, but the function names do not, so this lesson teaches the ideas with SQLite’s names and tells you where to look for your own.
Dates
Lesson 3 explained that dates stored as year-month-day text sort and compare correctly. Many databases also have real date types and a rich set of functions. Typical jobs are: finding the difference between two dates, adding days or months to a date, and pulling out a part, such as the month. In SQLite, julianday turns a date into a day count so you can subtract, date with a modifier such as +14 days adds time, and strftime cuts out parts. Other systems use functions such as DATEDIFF and DATE_ADD, simple subtraction or interval types. The functions are different, but the three jobs are the same.
Text
Text functions change or measure words. UPPER and LOWER change capitals, LENGTH counts characters, SUBSTR takes a slice, REPLACE swaps one piece of text for another and TRIM removes spaces from both ends. To join pieces of text, standard SQL uses two bars, as in name || ‘ (‘ || category || ‘)’. Some databases use a function called CONCAT instead, and a few give the double bar another meaning, so check yours. Names vary too: the length function is LEN in some systems, and the slice function may be SUBSTRING.
Six queries on dates and text
-- 1
SELECT c.name, c.joined, MIN(o.order_date) AS first_order,
CAST(julianday(MIN(o.order_date)) - julianday(c.joined) AS INTEGER) AS days_to_first_order
FROM customers AS c JOIN orders AS o ON o.customer_id = c.id GROUP BY c.id ORDER BY c.id
-- 2
SELECT id, order_date, date(order_date, '+14 days') AS payment_due FROM orders WHERE id <= 3
-- 3
SELECT name, UPPER(name) AS shout, LENGTH(name) AS letters, SUBSTR(name, 1, 3) AS short FROM products WHERE category = 'pot'
-- 4
SELECT name || ' (' || category || ')' AS label FROM products WHERE price > 15
-- 5
SELECT REPLACE(name, ' ', '_') AS slug, TRIM(' Leeds ') AS trimmed FROM products WHERE id IN (4, 7)
-- 6
SELECT name FROM products WHERE LOWER(name) LIKE '%pot%'
-- 1
name | joined | first_order | days_to_first_order
Asha | 2025-03-14 | 2026-06-03 | 446
Ben | 2025-06-02 | 2026-06-10 | 373
Chloe | 2025-09-21 | 2026-07-15 | 297
Dev | 2026-01-10 | 2026-08-02 | 204
Ella | 2026-02-18 | 2026-09-05 | 199
-- 2
id | order_date | payment_due
1 | 2026-06-03 | 2026-06-17
2 | 2026-06-10 | 2026-06-24
3 | 2026-07-01 | 2026-07-15
-- 3
name | shout | letters | short
Clay pot | CLAY POT | 8 | Cla
Glass pot | GLASS POT | 9 | Gla
-- 4
label
Orchid (plant)
-- 5
slug | trimmed
Clay_pot | Leeds
Mist_spray | Leeds
-- 6
name
Clay pot
Glass pot
Query 1 works out how many days each customer waited from joining to their first order. It covers 5 customers, because Femi has no orders and the join leaves them out. Asha joined on 2025-03-14 and first ordered on 2026-06-03, which is 446 days later. Ella waited 199. Query 2 adds 14 days to the order date to get a payment due date: order 1 becomes due on 2026-06-17, and order 3 on 2026-07-15. Query 3 shows text functions on the two pots: Clay pot becomes CLAY POT, has 8 letters and starts with Cla. Query 4 glues two columns into a label, Orchid (plant). Query 5 swaps spaces for underscores to make a short code, Clay_pot for the first product, and trims the spaces off a piece of text. Query 6 finds names containing ‘pot’ regardless of capitals by lowering the name first: Clay pot, Glass pot.
Where date and text work goes wrong
- Dates stored as text in odd formats. Day-first and month-first dates do not sort or subtract. Keep one format, ideally year-month-day.
- Time zones. A moment can fall on different dates in different places. Decide which zone your data uses and write it down.
- Hidden spaces and capitals. ‘York’ and ‘York ‘ and ‘york’ are different values to a database. Trim and standardise when you load the data.
- Functions on columns slow queries. Wrapping a column in LOWER() in a WHERE clause can stop an index being used, as lesson 19 explains.
- Leap years and month ends. Adding a month to the thirty-first of January needs a rule. Test dates at the edges.
Copying a date function from a web page written for another database. The code looks right and fails, or worse, runs and returns something different. Look up the function in your own system’s documentation.
The names and dates are made up. The function names here are SQLite’s. MySQL, PostgreSQL and SQL Server have their own, and some of the same words mean different things, so check the documentation for your database before relying on any of them.
Practice for this lesson
Check what you learned
Five quick questions. Pick an answer and you will see straight away whether it is right and why.
Why are date functions hard to copy between databases?
- Dates are not stored
- Only one database has dates
- Names and syntax differ between systems
- Dates never sort
The jobs are the same but the function names differ.
Which date format sorts correctly as text?
- Day-month-year
- Month-day-year
- Month names
- Year-month-day
Year-month-day order matches date order.
What does TRIM do?
- Shortens a number
- Removes spaces from the ends of text
- Sorts words
- Counts words
Query 5 trimmed the spaces off Leeds.
Why lower the name before matching “pot”?
- To make the name longer
- To remove NULLs
- So capital letters do not matter
- To sort the names
LOWER makes the comparison ignore capitals.
Why did Femi not appear in the waiting-time query?
- Femi has no orders, so the join left them out
- Femi has no name
- Femi joined too early
- The date was NULL
An inner join keeps only customers with orders.
Summary
- Date and text functions differ between databases.
- Keep dates in year-month-day form.
- Trim and standardise text on loading.
- Look up each function in your own documentation.
Questions
Which database has the standard date functions?
There is a standard, but databases follow it differently and add their own. Check your own system.
How do I join text in my database?
Standard SQL uses two bars. Some databases use CONCAT instead. Check your documentation.
Do these queries work everywhere?
The ideas do. The function names in this lesson are SQLite's, so translate them for your own database.
