Dates: DATEADD, DATEDIFF and DATE_TRUNC

Opening your lesson

Snowflake's date functions name the unit first:

DATEADD(day, 30, signup_date) adds 30 days. Units include day, week, month, quarter and year, and negative numbers go back.
DATEDIFF(day, start, end) counts unit boundaries between two dates. DATEDIFF(month, '2025-01-31', '2025-02-01') is 1, even though one day passed.
DATE_TRUNC('month', order_ts) rounds down to the start of the month, the usual way to group by month.
LAST_DAY(d) is the last day of its month, and DAYNAME(d) is Mon, Tue and so on.
TO_CHAR(d, 'YYYY-MM') formats a date as text.

CURRENT_DATE() is today, which in this account is 1 July 2025, just after the data ends.

SELECT DATE_TRUNC('month', order_ts) AS month, COUNT(*) AS orders
FROM orders
GROUP BY month
ORDER BY month;

Orders per month: every timestamp rounded down to the first of its month.

Choose your character