Strings and dates, engine by engine

Joining text

SELECT first_name || ' ' || last_name AS customer,
       country
FROM customers
LIMIT 10;

-- PostgreSQL, Oracle: identical to SQLite.
SELECT first_name || ' ' || last_name AS customer FROM customers;

-- MySQL / MariaDB: || means OR unless PIPES_AS_CONCAT is set.
SELECT CONCAT(first_name, ' ', last_name) AS customer FROM customers;

-- SQL Server: + concatenates, and CONCAT() exists from 2012.
SELECT first_name + ' ' + last_name AS customer FROM customers;
SELECT CONCAT(first_name, ' ', last_name) AS customer FROM customers;

|| is the standard concatenation operator and is used by PostgreSQL, Oracle, and SQLite. In MySQL it means logical OR unless PIPES_AS_CONCAT is enabled, so the expression returns 0 or 1 rather than failing. SQL Server uses +, which adds values when both operands are numeric.

CONCAT() is available in MySQL, MariaDB, PostgreSQL, and SQL Server (2012+), making it the most portable form across those engines. Its NULL behavior also differs from the operators.

Two behaviours differ, not just the spelling:

• NULL propagates through the operators. 'a' || NULL is NULL in PostgreSQL and SQLite, and 'a' + NULL is NULL in SQL Server. So one missing middle name empties the whole column. CONCAT() treats NULL as an empty string instead. Oracle's || also ignores NULL, because of the next point.
• Oracle treats the empty string as NULL. '' IS NULL is true there and false everywhere else, so a WHERE name = '' that finds rows on MySQL finds none on Oracle.

Formatting a date

SELECT order_number,
       ordered_at,
       strftime('%Y-%m', ordered_at) AS month,
       strftime('%d/%m/%Y', ordered_at) AS day_first
FROM orders
LIMIT 10;

-- MySQL / MariaDB
SELECT DATE_FORMAT(ordered_at, '%Y-%m') AS month FROM orders;

-- PostgreSQL, Oracle
SELECT to_char(ordered_at, 'YYYY-MM') AS month FROM orders;

-- SQL Server (2012+)
SELECT FORMAT(ordered_at, 'yyyy-MM') AS month FROM orders;
-- ...or, much faster in bulk, and the usual choice for grouping:
SELECT CONVERT(char(7), ordered_at, 126) AS month FROM orders;

Four different function names and three different pattern languages. strftime uses C-style %Y-%m, DATE_FORMAT uses the same percent codes with different meanings for some letters, to_char uses YYYY-MM, and FORMAT uses .NET's yyyy-MM — where the case matters, since mm is minutes.

For grouping rather than display, prefer whichever form the engine can compute cheaply: on SQL Server CONVERT(char(7), col, 126) is much faster than FORMAT over many rows, and on PostgreSQL date_trunc('month', col) beats to_char and keeps the result a date. And remember the indexes lesson: wrapping the column in any of these means the query cannot use an index on it.

Planche normalizes what comes back, whichever engine produced it. The drivers render dates as YYYY-MM-DD HH:MM:SS[.mmm] so the grid is consistent, and the Oracle driver sets NLS_DATE_FORMAT to match, so a TO_CHAR you write yourself agrees with what the grid shows next to it.

The gap between two dates

SELECT o.order_number,
       o.ordered_at,
       s.delivered_at,
       ROUND(julianday(s.delivered_at) - julianday(o.ordered_at), 1) AS days_to_deliver
FROM orders o
JOIN shipments s ON s.order_id = o.id
WHERE s.delivered_at IS NOT NULL
ORDER BY days_to_deliver DESC
LIMIT 15;

-- MySQL / MariaDB: whole days, first argument minus second.
SELECT DATEDIFF(delivered_at, ordered_at) AS days FROM ...;

-- PostgreSQL: subtracting two timestamps gives an interval.
SELECT delivered_at - ordered_at AS elapsed FROM ...;
SELECT EXTRACT(DAY FROM delivered_at - ordered_at) AS days FROM ...;

-- SQL Server: the unit comes first, and the arguments are the other way round.
SELECT DATEDIFF(day, ordered_at, delivered_at) AS days FROM ...;

-- Oracle: subtracting two DATEs gives a number of days.
SELECT delivered_at - ordered_at AS days FROM ...;

These disagree about more than the name. MySQL's DATEDIFF(a, b) takes two arguments and returns a - b in whole days. SQL Server's DATEDIFF(day, a, b) takes a unit first and returns b - a — the arguments are the other way round, so a query copied between the two returns the same number negated. PostgreSQL and Oracle use subtraction, giving an interval and a number of days respectively.

SQL Server's version also counts boundaries crossed, not elapsed time: DATEDIFF(day, '2025-01-01 23:00', '2025-01-02 01:00') is 1, for two hours apart.

Replacing a NULL

SELECT id,
       first_name,
       phone,
       COALESCE(phone, '(none)') AS portable,
       IFNULL(phone, '(none)')   AS sqlite_and_mysql
FROM customers
WHERE phone IS NULL
LIMIT 10;

-- Everywhere, and the one to reach for:
SELECT COALESCE(phone, '(none)') FROM customers;

-- MySQL / MariaDB / SQLite
SELECT IFNULL(phone, '(none)') FROM customers;

-- SQL Server. Note ISNULL, not IFNULL, and it takes exactly two arguments.
SELECT ISNULL(phone, '(none)') FROM customers;

-- Oracle
SELECT NVL(phone, '(none)') FROM customers;

COALESCE is standard, exists everywhere, and takes any number of arguments — returning the first that is not NULL. The engine-specific ones each take exactly two, and two of the names are close enough to swap by accident: MySQL and SQLite have IFNULL, SQL Server has ISNULL. There is no reason to use them over COALESCE in new code.

The short version

If a query has to run on more than one engine, CONCAT(), COALESCE() and CAST() cover most of what you need. Date formatting will not port, and is usually better done in the application, where you have the user's locale and timezone anyway.