First day and last day of month

-- First day of the month containing date_col.
SELECT DATE_TRUNC('month', order_date) AS first_day FROM orders;       -- Postgres / DuckDB / Snowflake / Redshift / Databricks
SELECT DATE_TRUNC(order_date, MONTH)   AS first_day FROM orders;       -- BigQuery (note arg order)
SELECT DATEFROMPARTS(YEAR(order_date), MONTH(order_date), 1) AS first_day FROM orders; -- SQL Server

-- Last day of the month containing date_col.
SELECT (DATE_TRUNC('month', order_date) + INTERVAL '1 month' - INTERVAL '1 day') AS last_day
FROM orders;                                                            -- Postgres / DuckDB

SELECT LAST_DAY(order_date) AS last_day FROM orders;                   -- MySQL / Oracle / Snowflake / DuckDB
SELECT LAST_DAY(order_date, MONTH) FROM orders;                        -- Snowflake / BigQuery (with unit)
SELECT EOMONTH(order_date) AS last_day FROM orders;                    -- SQL Server (since 2012)

-- First day of last month.
SELECT DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 month' AS first_of_last_month;

-- Last day of last month (= day before first of this month).
SELECT DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 day'   AS last_of_last_month;

-- Group orders by month and align the bucket label to the first of that month.
SELECT DATE_TRUNC('month', order_date) AS month_start, SUM(total) AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month_start;

-- Filter "this month so far" without inequality on a function.
SELECT * FROM orders
WHERE order_date >= DATE_TRUNC('month', CURRENT_DATE)
  AND order_date <  DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month';