String & Date Functions
Overview
Databases do more than just store raw text. SQL provides massive libraries of built-in functions to mutate strings (changing cases, extracting substrings) and execute complex Date/Time mathematics (calculating the days between two events, adding months to a subscription). Offloading this logic to the database is often significantly faster than formatting it in the frontend.
Syntax
-- 1. STRING FUNCTIONS
SELECT
UPPER(first_name) AS loud_name,
LOWER(email) AS safe_email,
LENGTH(password) AS pass_length,
-- Extracts 3 chars, starting at index 1
SUBSTRING(phone, 1, 3) AS area_code,
-- Glues strings together safely
CONCAT(first_name, ' ', last_name) AS full_name
FROM users;
-- 2. DATE FUNCTIONS (PostgreSQL Syntax)
SELECT
CURRENT_DATE, -- '2026-09-25'
CURRENT_TIMESTAMP, -- '2026-09-25 14:30:00'
-- Date Math! Add 30 days to a subscription
created_at + INTERVAL '30 days' AS expires_at,
-- Extract specific parts of a date
EXTRACT(YEAR FROM created_at) AS join_year
FROM subscriptions;Common Pitfalls
- Assuming Date Functions are standardized. While
SELECTandJOINare ANSI standard, Date logic varies violently between databases. PostgreSQL usesCURRENT_DATE, SQL Server usesGETDATE(), and MySQL usesNOW(). Always check the dialect documentation when working with time. - Timezone blindspots. Storing dates as raw strings or 'Local Time' is a disaster waiting to happen. If a server moves from NY to London, all relative time breaks. ALWAYS store timestamps as UTC (
TIMESTAMP WITH TIME ZONE), and let the frontend client convert it to local time for the user.
Interview Questions
UPPER(email) = 'TEST@GMAIL.COM' in a WHERE clause if the table has millions of rows?Wrapping a column in a function completely blinds the database's Indexes (known as SARGability). The database can no longer use its hyper-fast B-Tree index to find the email; it must pull every single row into CPU memory, run the UPPER() function on it, and then check it. Use Functional/Expression Indexes if you must do this.
Real-World Example
Finding all accounts that have been inactive for more than 1 year (Postgres syntax).
SELECT user_id, last_login
FROM users
WHERE last_login < CURRENT_DATE - INTERVAL '1 year';Check Your Knowledge
Test your understanding of String & Date Functions with these quick questions.