Cheatsheet

SQL Date & String Functions Cheatsheet

The everyday date and text functions, with dialect notes.

Dates

Function	Does
`current_date`	Today's date.
`date '2024-03-01'`	A literal date.
`date_trunc('month', d)`	Snap down to the first of the month.
`date_diff('day', a, b)`	Count date-part boundaries crossed from `a` to `b`.
`extract(year from d)`	Pull out a part (year, month, day…).
`d + interval 7 day`	Date math.
`strftime(d, '%Y-%m')`	Format a date as text.

Filter a month (half-open range)

where order_date >= date '2024-03-01'
  and order_date <  date '2024-04-01'

More ranges, week and month buckets, and date spines are in the date ranges and buckets cheatsheet.

Strings

Function	Does
`length(s)`	Number of characters.
`lower(s)` / `upper(s)`	Change case.
`trim(s)`	Remove surrounding spaces.
`left(s, n)` / `right(s, n)`	First / last `n` characters.
`substring(s, 2, 3)`	3 chars starting at position 2.
`a \|\| b`	Concatenate (glue) two strings.
`replace(s, 'x', 'y')`	Swap all `x` for `y`.
`split_part(s, '@', 2)`	The 2nd piece after splitting on `@`.

Build a login handle

select lower(left(first_name, 1) || last_name) as login
from   customers

Dialect notes

These examples are DuckDB SQL. Other databases vary in both function names and details. SQL Server has DATEDIFF; MySQL commonly uses CONCAT(...) instead of ||; date formatting uses different functions such as TO_CHAR in Postgres. Check the target database before copying date or string syntax unchanged.

Lesson

Filtering by Date Range in SQL

Use safe half-open date ranges.

Reference

SQL Date Functions

Date parts, math, and truncation.

Interview

SQL Date Range Interview Questions

Practice safe date filters, buckets, and lead-time reports.

Mistake

Why a Date Range Drops the Last Day

Use a half-open range, not BETWEEN, on timestamps.