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) | Whole units 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'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 customersDialect notes
These examples are DuckDB SQL. Other databases vary:date_diff is DATEDIFF in SQL Server;|| is CONCAT(...) in MySQL;strftime is TO_CHAR in Postgres andFORMAT in SQL Server.