Reference

SQL Date Functions

Get the current date, pull out parts, do date math, and bucket by period.

Prefer real DATE or TIMESTAMP types for date values. Text can sort chronologically when its format is carefully controlled, but it does not carry date semantics and can make comparisons and conversions fragile. Write date literals as DATE '2024-03-15' or explicitly cast from '2024-03-15'.

Practice

Practice comparing dates Practice date ranges Practice date math Practice filling missing dates with a date spine

Common functions (DuckDB)

Function	Returns
`current_date`	Today's date, in your browser's time zone.
`now()`	Current timestamp.
`date_part('month', d)`	A numeric part (year, month, day, dow…).
`extract(year from d)`	Same idea, SQL-standard spelling.
`date_trunc('month', d)`	Round down to start of period.
`datediff('day', a, b)`	Date-part boundaries crossed from `a` to `b`: January 31 to February 1 is one month.
`d + interval 7 day`	Date math with intervals.
`strftime(d, '%Y-%m')`	Format a date as text.

Filtering by date range

Half-open range is safest

select *
from   orders
where  order_date >= date '2024-03-01'
  and  order_date <  date '2024-04-01'

A half-open range (>= start, < next period) avoids the timestamp bug where BETWEEN ... and the last day misses orders placed later that same day.

Bucketing by month

Orders by month

select   date_trunc('month', order_date) as month,
         count(*) as orders
from     orders
group by month
order by month

Weeks, custom bucket sizes, and filling in months with no rows are on the date buckets and spines page.

Dialect note

Function names vary a lot by engine. Postgres uses EXTRACT and AGE; SQL Server uses DATEPART and DATEDIFF; SQLite uses strftime for almost everything. The concepts (parts, truncation, intervals) carry over.

Lesson

Why SQL Data Types Matter

See why text, numbers, dates, and booleans behave differently.

Cheatsheet

SQL Date and String Functions Cheatsheet

Everyday date and text functions.

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.