Skip to content
SQL

SQL CASE WHEN, CAST and Common String and Date Functions

Ihsan Ullah · Updated · 14 min read
SQL CASE WHEN cover for Lesson 8 of the free SQL course: CASE, CAST and common string and date functions

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 CAST work
  • the most useful text functions and how || treats NULL
  • how to write CASE WHEN, including in ORDER BY
  • how to work with dates, and why date_trunc on 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.

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

TaskPostgreSQLMySQLSQLiteSQL Server
Convert a typeCAST(x AS t) or x::tCAST(x AS t)CAST(x AS t)CAST(x AS t)
First day of the monthdate_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 / 2 gives 3 with no warning. Cast one side: 7::numeric / 2.
  • Casting text that is not a number. SELECT 'abc'::integer; fails with ERROR: invalid input syntax for type integer: "abc".
  • CASE results of different types. CASE WHEN price > 100 THEN 'expensive' ELSE price END fails with ERROR: invalid input syntax for type numeric: "expensive". All results must fit one type; use price::text in the ELSE.
  • Forgetting END. CASE WHEN price > 100 THEN 'expensive' ELSE 'normal' AS label fails with ERROR: syntax error at or near "AS". Every CASE ends with END.
  • 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) or x::type converts types, and casts can round or truncate depending on the database.
  • upper, lower, initcap, length and trim clean text; || joins it but returns NULL if any part is NULL, while concat skips NULLs.
  • CASE WHEN ... THEN ... ELSE ... END returns the first match; order the conditions from narrow to wide, and remember that no ELSE means NULL.
  • EXTRACT, date_trunc(...)::date, date + n, date - date and age cover most date work; avoid CURRENT_DATE when you need repeatable results.
  • ||, :: and date_trunc are 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

Ihsan Ullah

Written & tested by Ihsan Ullah

Data analyst with 3 years of experience and a Bachelor's degree in Computer Science. I write clear, practical guides on data, SQL, Python and AI.

More about me →

Leave a comment

Your email address will not be published. Required fields are marked *