datesql

Ranges

First & Last Day of the Month in SQL (per Dialect)

2 min · updated June 14, 2026

Month boundaries — for billing periods, monthly buckets, and “month to date”.

First day of the month

SELECT DATE_FORMAT(d, '%Y-%m-01');                 -- MySQL
SELECT DATE_TRUNC('month', d)::date;               -- PostgreSQL / DuckDB
SELECT DATE_TRUNC(d, MONTH);                        -- BigQuery
SELECT DATE_TRUNC('month', d)::date;               -- Snowflake
SELECT DATE(d, 'start of month');                  -- SQLite

Last day of the month

-- Built-in LAST_DAY (MySQL, BigQuery, Snowflake, DuckDB)
SELECT LAST_DAY(d);

-- PostgreSQL  (first of next month minus a day)
SELECT (DATE_TRUNC('month', d) + INTERVAL '1 month - 1 day')::date;

-- SQLite
SELECT DATE(d, 'start of month', '+1 month', '-1 day');

Days in the month

SELECT DAY(LAST_DAY(d));                            -- MySQL / Snowflake / DuckDB
SELECT EXTRACT(DAY FROM (DATE_TRUNC('month', d) + INTERVAL '1 month - 1 day'));  -- Postgres

Gotchas: Postgres has no LAST_DAY before v15 — the “next month minus a day” form works everywhere and is the portable fallback. DATE_TRUNC('month', d) returns a timestamp in some engines; cast to ::date if you want a pure date. LAST_DAY correctly handles February in leap years, so prefer it where available.

← All recipes