What are common string and date functions?

Question

Accepted Answer

SQL provides built-in functions for manipulating **strings** and **dates** — formatting, extracting parts, computing differences. These are essential for data transformation and date-based queries, though exact names vary by database dialect.

## Common string functions

```sql
SELECT
  UPPER(name), LOWER(name),               -- case conversion
  LENGTH(name),                           -- string length
  CONCAT(first_name, ' ', last_name),      -- join strings (or || in some DBs)
  SUBSTRING(name, 1, 3),                   -- extract a substring
  TRIM(name),                              -- remove surrounding whitespace
  REPLACE(phone, '-', ''),                 -- replace text
  LEFT(name, 3), RIGHT(name, 3),           -- leftmost/rightmost chars
  POSITION('@' IN email)                   -- find a substring's position
FROM users;
```

String functions transform and extract text — useful for formatting output, cleaning data, and building computed values.

## Common date/time functions

```sql
SELECT
  NOW(), CURRENT_DATE, CURRENT_TIMESTAMP,   -- current date/time
  EXTRACT(YEAR FROM created_at),            -- get a part (YEAR, MONTH, DAY, HOUR...)
  DATE_TRUNC('month', created_at),          -- truncate to a unit (Postgres)
  created_at + INTERVAL '7 days',           -- date arithmetic (add time)
  AGE(NOW(), birth_date),                   -- difference between dates (Postgres)
  TO_CHAR(created_at, 'YYYY-MM-DD')         -- format a date as text (Postgres)
FROM events;
```

Date functions get the current time, extract parts (year, month), do arithmetic (add/subtract intervals), compute differences, and format dates — essential for time-based queries and reporting.

## A common pattern: grouping by time period

```sql
-- count events per month (a frequent reporting need)
SELECT
  DATE_TRUNC('month', created_at) AS month,
  COUNT(*) AS event_count
FROM events
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;
```

Grouping by an extracted/truncated date part (month, day, year) is a very common reporting pattern.

## Dialect differences (an important caveat)

```text
⚠️ Function names/syntax VARY significantly by database:
  String concat: CONCAT() vs || vs +
  Date functions: DATE_TRUNC/EXTRACT (Postgres) vs DATE_FORMAT (MySQL) vs DATEPART (SQL Server)
→ Check YOUR database's documentation; the concepts transfer, the exact syntax differs.
```

## Why it matters

String and date functions are essential, practically-needed SQL tools — manipulating text and working with dates are constant requirements in real queries (formatting output, cleaning data, time-based filtering and reporting), so understanding them is valuable everyday knowledge. **String functions** (case conversion, concatenation, substring, replace, trim) handle text transformation and cleaning, which appear in many queries for formatting and data manipulation. **Date functions** are particularly important because **time-based queries and reporting are ubiquitous** — getting the current time, extracting date parts (year, month), date arithmetic (adding intervals, computing differences), and formatting dates are needed for filtering by date ranges, calculating ages/durations, and especially **grouping by time period** (counts per month/day — a very common reporting pattern).

Understanding these functions lets you handle the text and date manipulation that real applications constantly require.

An important practical caveat is that **function names and syntax vary significantly by database dialect** (string concatenation and date functions differ notably between PostgreSQL, MySQL, SQL Server) — so knowing the concepts (which transfer) while checking your specific database's syntax is important.

Since manipulating strings and dates is a frequent need in querying, reporting, and data processing, and since date-based grouping/filtering is central to analytics, understanding common string and date functions — and being aware of dialect differences — is useful, frequently-applied SQL knowledge essential for practical data work.