Reference

SQL Date Functions

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

Dates compare and sort correctly only when stored as a realDATE or TIMESTAMP, not as text. Write date literals as DATE '2024-03-15' or rely on automatic casting from'2024-03-15'.

Common functions (DuckDB)

Function	Returns
`current_date`	Today's date.
`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)`	Whole units between two dates.
`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 classic timestamp bug where BETWEEN ... and the last day misses orders placed later that same day.

Bucketing by month

Revenue by month

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

Dialect note

Function names vary a lot by engine. Postgres uses EXTRACT andAGE; SQL Server uses DATEPART andDATEDIFF; 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.

Lesson

Numbers and Calculations in SQL

Compare, sort, and calculate with numeric values.

Lesson

Why SQL Strings Need Quotes

Filter text values with string literals.

Lesson

Working with Dates and Times in SQL

Compare and sort date values chronologically.