SQL course · Module 2: Querying one table · Lesson 8 of 40
Quick answer: SQL CASE WHEN returns a value based on conditions, like if/else: CASE WHEN price < 20 THEN 'budget' ELSE 'standard' END. The first true WHEN wins, and without ELSE the result is NULL. CAST(price AS integer) (or price::integer in PostgreSQL) converts a value to another type. Common functions include upper, lower, initcap, length and trim for text, || to join text, and CURRENT_DATE, EXTRACT, date_trunc and age for dates.
In this lesson you will learn:
- how arithmetic, integer division and
CASTwork - the most useful text functions and how
||treats NULL - how to write
CASE WHEN, including inORDER BY - how to work with dates, and why
date_truncon a date shows a time zone - which of these differ in MySQL, SQLite and SQL Server
Before you start: Lesson 7, SQL ORDER BY: Sorting, NULLS FIRST/LAST and Paging with LIMIT/OFFSET.
We ran every example on PostgreSQL 16.15 with the shop database from Lesson 3. The functions used here are described the same way in the current PostgreSQL 18 manual, so they work the same on PostgreSQL 17 and 18.
Table of Contents
Arithmetic and integer division
You already used * in Lesson 4. Division needs care:
SELECT 7 / 2 AS int_division, 7 / 2.0 AS numeric_division, 7 % 2 AS remainder;
int_division | numeric_division | remainder
--------------+--------------------+-----------
3 | 3.5000000000000000 | 1
(1 row)
7 / 2 is 3, not 3.5. “For integral types, division truncates the result towards zero” (PostgreSQL docs: Mathematical Functions). If one side has decimals, the result keeps them. % gives the remainder.
CAST and the :: operator: change a value’s type
PostgreSQL “accepts two equivalent syntaxes for type casts”: CAST(expression AS type) and expression::type. “The CAST syntax conforms to SQL; the syntax with :: is historical PostgreSQL usage” (PostgreSQL docs: Value Expressions).
SELECT price, CAST(price AS integer) AS cast_int, price::integer AS colon_int
FROM products WHERE id IN (1, 3, 7) ORDER BY id;
price | cast_int | colon_int
-------+----------+-----------
24.99 | 25 | 25
39.50 | 40 | 40
6.75 | 7 | 7
(3 rows)
In PostgreSQL the cast to integer rounded 24.99 up to 25 (and 6.75 up to 7). Do not expect that everywhere: SQL Server’s docs list numeric to int as “Truncate”, so CAST(10.6496 AS INT) gives 10 there (Microsoft Learn: CAST and CONVERT), and in our SQLite 3.53.1 test CAST(24.99 AS INTEGER) gave 24. Use round() when you mean rounding.
A cast also fixes integer division:
SELECT 7::numeric / 2 AS half, round(7::numeric / 3, 2) AS third;
half | third
--------------------+-------
3.5000000000000000 | 2.33
(1 row)
And it turns text into a date, so you can do date arithmetic. Adding a number to a date adds days:
SELECT '2026-11-04'::date AS d, '2026-11-04'::date + 30 AS plus_30_days;
d | plus_30_days
------------+--------------
2026-11-04 | 2026-12-04
(1 row)
Text functions
The shop has two badly typed names, customers 4 and 8. Text functions can clean them up in the output:
SELECT id, name, upper(name), lower(name), initcap(name), length(name)
FROM customers WHERE id IN (1, 4, 8) ORDER BY id;
id | name | upper | lower | initcap | length
----+--------------+--------------+--------------+--------------+--------
1 | Ayesha Malik | AYESHA MALIK | ayesha malik | Ayesha Malik | 12
4 | bilal ahmed | BILAL AHMED | bilal ahmed | Bilal Ahmed | 11
8 | NOAH WILSON | NOAH WILSON | noah wilson | Noah Wilson | 11
(3 rows)
initcap “converts the first letter of each word to upper case and the rest to lower case”, and length “returns the number of characters in the string” (PostgreSQL docs: String Functions). The space counts as a character.
trim removes spaces from both ends, a common clean-up for data typed by people:
SELECT ' Lahore ' AS raw, length(' Lahore ') AS raw_length,
trim(' Lahore ') AS trimmed, length(trim(' Lahore ')) AS trimmed_length;
raw | raw_length | trimmed | trimmed_length
------------+------------+---------+----------------
Lahore | 10 | Lahore | 6
(1 row)
Joining text with ||
SELECT id, initcap(name) || ' (' || country || ')' AS label FROM customers ORDER BY id LIMIT 4;
id | label
----+-------------------------
1 | Ayesha Malik (Pakistan)
2 | Lucas Martin (Canada)
3 | Sofia Rossi (Italy)
4 | Bilal Ahmed (Pakistan)
(4 rows)
As Lesson 6 showed, || with a NULL gives NULL. The concat function instead ignores NULL arguments:
SELECT id, name || ' <' || email || '>' AS with_pipes, concat(name, ' <', email, '>') AS with_concat
FROM customers WHERE id IN (4, 5) ORDER BY id;
id | with_pipes | with_concat
----+---------------------------------------+---------------------------------------
4 | bilal ahmed <bilal.ahmed@example.com> | bilal ahmed <bilal.ahmed@example.com>
5 | (null) | Emma Clarke <>
(2 rows)
(We used \pset null '(null)' from Lesson 6 to show the NULL.) Neither result is ideal for Emma; COALESCE(email, 'no email') inside the || chain is often the best choice.
CASE WHEN: if/else inside a query
A searched CASE checks conditions in order and returns the result of the first true one:
SELECT name, price,
CASE
WHEN price < 20 THEN 'budget'
WHEN price < 100 THEN 'standard'
ELSE 'premium'
END AS price_band
FROM products
ORDER BY price;
name | price | price_band
-----------------------------+--------+------------
Gel Pens 10-Pack | 6.75 | budget
Notebook Pack | 12.50 | budget
Wireless Mouse | 24.99 | standard
Desk Lamp | 32.00 | standard
USB-C Hub | 39.50 | standard
Laptop Stand | 45.00 | standard
HD Webcam | 54.90 | standard
Mechanical Keyboard | 89.00 | standard
Noise-Cancelling Headphones | 149.00 | premium
Ergonomic Chair | 259.00 | premium
(10 rows)
The Wireless Mouse (24.99) is less than 100, but it is not less than 20, so the second WHEN matches. Because the first true WHEN wins, the order matters. Put the wide condition first and the narrow one never runs:
SELECT name, price,
CASE
WHEN price < 100 THEN 'standard'
WHEN price < 20 THEN 'budget'
ELSE 'premium'
END AS price_band
FROM products
ORDER BY price
LIMIT 3;
name | price | price_band
------------------+-------+------------
Gel Pens 10-Pack | 6.75 | standard
Notebook Pack | 12.50 | standard
Wireless Mouse | 24.99 | standard
(3 rows)
No product is called “budget” any more. Write the narrowest condition first.
No ELSE means NULL
“If the ELSE clause is omitted and no condition is true, the result is null” (PostgreSQL docs: Conditional Expressions):
SELECT id, status, CASE WHEN status = 'delivered' THEN 'done' END AS note
FROM orders WHERE id >= 19 ORDER BY id;
id | status | note
----+---------+--------
19 | shipped | (null)
20 | shipped | (null)
21 | shipped | (null)
22 | pending | (null)
(4 rows)
Simple CASE
When every test compares one value with a constant, the simple form is shorter. It “is similar to the switch statement in C”:
SELECT id, status,
CASE status
WHEN 'pending' THEN 'open'
WHEN 'shipped' THEN 'open'
WHEN 'delivered' THEN 'closed'
ELSE 'cancelled'
END AS stage
FROM orders WHERE id IN (4, 18, 19, 22) ORDER BY id;
id | status | stage
----+-----------+-----------
4 | cancelled | cancelled
18 | delivered | closed
19 | shipped | open
22 | pending | open
(4 rows)
CASE in ORDER BY
Sorting status alphabetically gives cancelled, delivered, pending, shipped. That is not the order a business thinks in. CASE turns each status into a number to sort by:
SELECT id, status
FROM orders
WHERE id >= 17
ORDER BY CASE status
WHEN 'pending' THEN 1
WHEN 'shipped' THEN 2
WHEN 'delivered' THEN 3
ELSE 4
END,
id;
id | status
----+-----------
22 | pending
19 | shipped
20 | shipped
21 | shipped
17 | delivered
18 | delivered
(6 rows)
The same trick places NULLs where you want them, even in databases without NULLS FIRST (see Lesson 7):
SELECT id, name, city FROM customers
ORDER BY CASE WHEN city IS NULL THEN 0 ELSE 1 END, city, id
LIMIT 3;
id | name | city
----+---------------+---------
7 | Hira Siddiqui | (null)
6 | Daniel Weber | Berlin
8 | NOAH WILSON | Chicago
(3 rows)
Date functions
CURRENT_DATE returns today’s date, so its result changes every day:
SELECT CURRENT_DATE;
current_date
--------------
2026-11-04
(1 row)
We ran this on 4 November 2026; you will see your own date. To keep the examples below repeatable, we use the fixed date DATE '2026-11-04' as “today” instead of CURRENT_DATE.
EXTRACT: one part of a date
SELECT id, order_date,
EXTRACT(YEAR FROM order_date) AS year,
EXTRACT(MONTH FROM order_date) AS month
FROM orders WHERE id IN (1, 10, 22) ORDER BY id;
id | order_date | year | month
----+------------+------+-------
1 | 2026-06-03 | 2026 | 6
10 | 2026-08-04 | 2026 | 8
22 | 2026-10-30 | 2026 | 10
(3 rows)
“The extract function returns values of type numeric” (PostgreSQL docs: Date/Time Functions).
date_trunc: round down to the month
date_trunc('month', ...) gives the first day of the month, which is how you group sales by month in Lesson 10. On a date column it has a surprise:
SELECT id, order_date, date_trunc('month', order_date) AS month_start
FROM orders WHERE id IN (1, 10, 22) ORDER BY id;
id | order_date | month_start
----+------------+------------------------
1 | 2026-06-03 | 2026-06-01 00:00:00+05
10 | 2026-08-04 | 2026-08-01 00:00:00+05
22 | 2026-10-30 | 2026-10-01 00:00:00+05
(3 rows)
The result has a time and +05, the UTC offset of our server’s time zone (Asia/Karachi). pg_typeof shows why:
SELECT pg_typeof(date_trunc('month', order_date)) FROM orders LIMIT 1;
pg_typeof
--------------------------
timestamp with time zone
(1 row)
date_trunc has no version for date, so PostgreSQL first converts the date to a timestamp. The docs say a date is cast to timestamp, but in our run PostgreSQL picked timestamp with time zone, which is the preferred date/time type when it chooses between function versions (PostgreSQL docs: Type Conversion, Functions). So the result is midnight in your session’s time zone, shown with that zone’s offset, and your output depends on the server’s TimeZone setting (type SHOW timezone; to see yours). With the time zone set to UTC we got 2026-06-01 00:00:00+00, and with America/New_York 2026-06-01 00:00:00-04. Cast the result back to a date; this gives the same answer in every time zone:
SELECT id, order_date, date_trunc('month', order_date)::date AS month_start
FROM orders WHERE id IN (1, 10, 22) ORDER BY id;
id | order_date | month_start
----+------------+-------------
1 | 2026-06-03 | 2026-06-01
10 | 2026-08-04 | 2026-08-01
22 | 2026-10-30 | 2026-10-01
(3 rows)
Date arithmetic and AGE
date + integer adds days, and date - date gives “the number of days elapsed”:
SELECT id, order_date, order_date + 7 AS deliver_by,
DATE '2026-11-04' - order_date AS days_ago
FROM orders WHERE id IN (1, 10, 22) ORDER BY id;
id | order_date | deliver_by | days_ago
----+------------+------------+----------
1 | 2026-06-03 | 2026-06-10 | 154
10 | 2026-08-04 | 2026-08-11 | 92
22 | 2026-10-30 | 2026-11-06 | 5
(3 rows)
age(a, b) subtracts too, but gives “a ‘symbolic’ result that uses years and months”:
SELECT name, signup_date, age(DATE '2026-11-04', signup_date) AS member_for
FROM customers ORDER BY id;
name | signup_date | member_for
---------------+-------------+-----------------
Ayesha Malik | 2025-11-03 | 1 year 1 day
Lucas Martin | 2025-12-14 | 10 mons 21 days
Sofia Rossi | 2026-01-05 | 9 mons 30 days
bilal ahmed | 2026-02-20 | 8 mons 12 days
Emma Clarke | 2026-03-02 | 8 mons 2 days
Daniel Weber | 2026-04-11 | 6 mons 23 days
Hira Siddiqui | 2026-05-25 | 5 mons 10 days
NOAH WILSON | 2026-06-08 | 4 mons 26 days
Chloe Dubois | 2026-07-19 | 3 mons 16 days
Omar Haddad | 2026-09-02 | 2 mons 2 days
(10 rows)
mons means months. With one argument, age(signup_date) subtracts from current_date, so its result changes every day.
Dialect differences
| Task | PostgreSQL | MySQL | SQLite | SQL Server |
|---|---|---|---|---|
| Convert a type | CAST(x AS t) or x::t | CAST(x AS t) | CAST(x AS t) | CAST(x AS t) |
| First day of the month | date_trunc('month', d) | DATE_FORMAT(d, '%Y-%m-01') (returns text) | date(d, 'start of month') | DATETRUNC(month, d) from SQL Server 2022 |
To join text, SQLite uses || like PostgreSQL. SQL Server uses + or CONCAT(), and accepts || from SQL Server 2025. The biggest trap is MySQL, where || means logical OR unless the PIPES_AS_CONCAT SQL mode is on, so use CONCAT() there (MySQL 9.7 manual: Logical Operators). MySQL’s / also keeps decimals, and DIV does whole-number division (MySQL 9.7 manual: Arithmetic Operators); our SQLite 3.53.1 test gave 3 for 7 / 2, like PostgreSQL. MySQL has no date_trunc and uses CAST(x AS SIGNED) for integers (MySQL 9.7 manual: Date and Time Functions, Cast Functions). Other sources: SQLite: Date and Time Functions, Microsoft Learn: || operator and DATETRUNC. CASE and COALESCE are standard SQL and exist in all four. Lesson 2 compares these databases in more detail.
Try it yourself
Exercise 1: Clean customer labels
For every customer, show the id and one column customer like Bilal Ahmed, PAKISTAN: the name with capital first letters, a comma, and the country in capitals.
Exercise 2: Open or closed?
For orders placed from 1 October 2026, show the id, status and a stage column: open for pending or shipped, closed for delivered, cancelled otherwise.
Exercise 3: New or regular customers
Using 4 November 2026 as today, show each customer’s name, signup date, number of days as a customer, and new if under 180 days, otherwise regular. Sort by signup date.
Solutions
Solution 1
SELECT id, initcap(name) || ', ' || upper(country) AS customer FROM customers ORDER BY id;
id | customer
----+-------------------------
1 | Ayesha Malik, PAKISTAN
2 | Lucas Martin, CANADA
3 | Sofia Rossi, ITALY
4 | Bilal Ahmed, PAKISTAN
5 | Emma Clarke, UK
6 | Daniel Weber, GERMANY
7 | Hira Siddiqui, PAKISTAN
8 | Noah Wilson, USA
9 | Chloe Dubois, FRANCE
10 | Omar Haddad, PAKISTAN
(10 rows)
initcap fixes both bilal ahmed and NOAH WILSON.
Solution 2
SELECT id, status,
CASE
WHEN status IN ('pending', 'shipped') THEN 'open'
WHEN status = 'delivered' THEN 'closed'
ELSE 'cancelled'
END AS stage
FROM orders
WHERE order_date >= '2026-10-01'
ORDER BY id;
id | status | stage
----+-----------+--------
18 | delivered | closed
19 | shipped | open
20 | shipped | open
21 | shipped | open
22 | pending | open
(5 rows)
A WHEN can use any condition from Lesson 5, including IN.
Solution 3
SELECT name, signup_date,
DATE '2026-11-04' - signup_date AS days_as_customer,
CASE WHEN DATE '2026-11-04' - signup_date < 180 THEN 'new' ELSE 'regular' END AS customer_type
FROM customers
ORDER BY signup_date;
name | signup_date | days_as_customer | customer_type
---------------+-------------+------------------+---------------
Ayesha Malik | 2025-11-03 | 366 | regular
Lucas Martin | 2025-12-14 | 325 | regular
Sofia Rossi | 2026-01-05 | 303 | regular
bilal ahmed | 2026-02-20 | 257 | regular
Emma Clarke | 2026-03-02 | 247 | regular
Daniel Weber | 2026-04-11 | 207 | regular
Hira Siddiqui | 2026-05-25 | 163 | new
NOAH WILSON | 2026-06-08 | 149 | new
Chloe Dubois | 2026-07-19 | 108 | new
Omar Haddad | 2026-09-02 | 63 | new
(10 rows)
date - date gives whole days, which CASE compares with 180. With CURRENT_DATE instead of the fixed date, the numbers grow every day.
Common mistakes
- Integer division.
7 / 2gives 3 with no warning. Cast one side:7::numeric / 2. - Casting text that is not a number.
SELECT 'abc'::integer;fails withERROR: invalid input syntax for type integer: "abc". - CASE results of different types.
CASE WHEN price > 100 THEN 'expensive' ELSE price ENDfails withERROR: invalid input syntax for type numeric: "expensive". All results must fit one type; useprice::textin theELSE. - Forgetting END.
CASE WHEN price > 100 THEN 'expensive' ELSE 'normal' AS labelfails withERROR: syntax error at or near "AS". EveryCASEends withEND. - Leaving date_trunc as a timestamp. On a date column it shows a time and a time-zone offset. Add
::date.
Frequently asked questions
Can I use CASE WHEN in a WHERE clause?
Yes, CASE is an expression and can go anywhere a value can: the select list, WHERE, ORDER BY and later GROUP BY. In WHERE, plain AND and OR are usually easier to read, so use CASE there only when the logic really needs it.
What is the difference between CAST and :: in PostgreSQL?
They do the same thing. CAST(x AS type) is standard SQL and works in other databases; x::type is shorter, but of the four databases in this course only PostgreSQL accepts it (our SQLite test failed with unrecognized token: ":"). Use CAST in code that may move to another database.
How do I get the month from a date in SQL?
Use EXTRACT(MONTH FROM order_date) for the month number, or date_trunc('month', order_date)::date for the first day of the month, which is better for grouping because it keeps the year. MySQL, SQLite and SQL Server use other functions, shown in the table above.
Summary
/between integers drops the fraction;CAST(x AS type)orx::typeconverts types, and casts can round or truncate depending on the database.upper,lower,initcap,lengthandtrimclean text;||joins it but returns NULL if any part is NULL, whileconcatskips NULLs.CASE WHEN ... THEN ... ELSE ... ENDreturns the first match; order the conditions from narrow to wide, and remember that noELSEmeans NULL.EXTRACT,date_trunc(...)::date,date + n,date - dateandagecover most date work; avoidCURRENT_DATEwhen you need repeatable results.||,::anddate_truncare the parts most likely to change in MySQL, SQLite and SQL Server.
Labels with CASE and month buckets with date_trunc appear in almost every report in the data analyst roadmap, and in many SQL window functions practice problems.
Next lesson
Next: Lesson 9, SQL Aggregate Functions: COUNT, SUM, AVG, MIN and MAX.
Sources
- PostgreSQL documentation: Value Expressions (type casts)
- PostgreSQL documentation: Mathematical Functions and Operators
- PostgreSQL documentation: String Functions and Operators
- PostgreSQL documentation: Conditional Expressions (CASE)
- PostgreSQL documentation: Date/Time Functions and Operators
- PostgreSQL documentation: Type Conversion, Functions
- MySQL 9.7 Reference Manual: Logical Operators
- MySQL 9.7 Reference Manual: Arithmetic Operators
- MySQL 9.7 Reference Manual: Date and Time Functions
- MySQL 9.7 Reference Manual: Cast Functions and Operators
- SQLite: Date and Time Functions
- Microsoft Learn: CAST and CONVERT
- Microsoft Learn: || (String concatenation)
- Microsoft Learn: DATETRUNC





