Date and time functions

DATEADD, DATEDIFF, EXTRACT, truncation and date ranges that do not leak.

What you'll be able to do

Extract parts of a date
Shift and difference dates
Write date filters that handle timestamps correctly

Extracting parts

EXTRACT(YEAR  FROM order_date)     -- standard
EXTRACT(MONTH FROM order_date)
DATE_PART('dow', order_date)       -- Postgres, day of week
YEAR(order_date)                   -- MySQL, SQL Server

Date handling is the least portable area of SQL, so it is worth knowing which dialect you are writing for.

Truncating

DATE_TRUNC('month', order_date)              -- Postgres: 2024-03-01
DATE_FORMAT(order_date, '%Y-%m-01')          -- MySQL
DATEFROMPARTS(YEAR(d), MONTH(d), 1)          -- SQL Server

Truncation is what you group by for a monthly trend — it keeps the value a real date, so it sorts correctly. Grouping by a formatted string sorts alphabetically, which puts October before February.

Shifting and differencing

DATEADD(month, 4, order_date)                -- SQL Server
order_date + INTERVAL '4 months'             -- Postgres
DATEDIFF(day, start_date, end_date)           -- SQL Server
end_date - start_date                         -- Postgres, gives days

Date ranges that do not leak

The single most common date bug:

-- misses anything after midnight on the 31st
WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31'

-- correct
WHERE order_date >= '2024-01-01'
  AND order_date <  '2024-02-01'

If the column is a timestamp, '2024-01-31' means 2024-01-31 00:00:00, so almost the whole final day is excluded. The half-open range is right whether or not there is a time component.

And avoid WHERE YEAR(order_date) = 2024, which cannot use an index — express it as a range instead.

Key points

Group by a truncated date, not a formatted string, so it sorts correctly
Use half-open ranges for dates, never BETWEEN on timestamps
YEAR(col) = 2024 in WHERE prevents an index seek

Check yourself