SQL Date Functions: How to Group by Month Without Losing One

1. A date in SQL is a real type

Before the explanation: in Excel a date is a number wearing a format. Say what you think it is in a database.

Its own type, with its own rules.

SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'orders' AND column_name = 'order_date';

order_date | DATE

That matters more than it sounds. Because the engine knows the column is a date, it can sort it chronologically without being told, compare it to another date, subtract one from another and give you days, and refuse a value that is not a valid date instead of storing it as text. None of that is available on a column of strings that happen to look like dates.

Three types come up, and it is worth being able to name them. DATE is a calendar day with no time. TIMESTAMP is a day and a time. TIMESTAMP WITH TIME ZONE is a day, a time and a position on the globe, and it is the one that turns a Monday report into a Sunday report for somebody in another office. If your source column is a timestamp and your question is about days, cast it early and say so.

2. DATE_TRUNC, and grouping by month

DATE_TRUNC takes a unit and a date and throws away everything smaller than that unit. Month truncation turns 19 January into 1 January, which makes every January date identical, which is what lets them be grouped.

SELECT DATE_TRUNC('month', order_date) AS month,
       SUM(units * unit_price)          AS revenue,
       COUNT(*)                         AS orders
FROM orders
GROUP BY 1
ORDER BY 1;

2026-01-01 | 2630 | 4
2026-02-01 | 2245 | 4
2026-03-01 | 2585 | 4
2026-05-01 | 2430 | 4

Four rows, and the revenue adds to 9,890, which is the table's total, so no rows escaped the grouping. Two things about the output are worth noticing before anything else.

The month is a date, not a label. That is the whole reason to prefer DATE_TRUNC to a string: the result still sorts chronologically, can still be compared, and can still be joined to a calendar. Some engines, DuckDB among them, return a timestamp from DATE_TRUNC and display it with a zero time on the end; cast it back with ::DATE if the trailing zeros bother you.

And there are four rows for a period that runs from January to May. Hold that thought until section six.

The string alternative works and gives up the type:

SELECT strftime(order_date, '%Y-%m') AS month, SUM(units * unit_price)
FROM orders GROUP BY 1 ORDER BY 1;

2026-01 | 2630
2026-02 | 2245
2026-03 | 2585
2026-05 | 2430

Same numbers, and '2026-01' is now text. It happens to sort correctly because the format puts the year first, which is luck rather than design, and it can no longer be used in date arithmetic. Use it for display at the very end, not for grouping.

Everything about grain from GROUP BY and HAVING applies here unchanged: after this query one row is one month, and an individual order no longer exists in the result.

3. Getting the pieces out

EXTRACT pulls a component out as a number, and most engines add friendlier helpers alongside it.

SELECT order_date,
       EXTRACT(YEAR  FROM order_date) AS y,
       EXTRACT(MONTH FROM order_date) AS m,
       EXTRACT(DAY   FROM order_date) AS d,
       DAYNAME(order_date)            AS dow
FROM orders LIMIT 1;

2026-01-05 | 2026 | 1 | 5 | Monday

One warning that costs people a whole report. EXTRACT(MONTH FROM ...) returns 1 to 12 with no year attached, so grouping by it merges January 2025 with January 2026 into a single row labelled 1. That is fine for a seasonality question and wrong for a trend, and the two look identical on the page. If you group by month number, group by year as well, or use DATE_TRUNC and avoid the choice.

4. Filtering a period, the half-open way

Before the explanation: you want March. Say which of these you would write, and whether they differ.

WHERE order_date BETWEEN '2026-03-01' AND '2026-03-31'
WHERE order_date >= DATE '2026-03-01' AND order_date < DATE '2026-04-01'

On a pure DATE column they agree, and the second returned 2,585 for March in the run. On a TIMESTAMP column they do not agree at all, because an order at 09:14 on 31 March is later than 31 March at midnight, so BETWEEN excludes it. A whole day of activity disappears, and only on months whose last day happens to be busy.

So adopt the second form as a habit even where the first would work. Greater than or equal to the start, strictly less than the start of the next period. It is correct for dates and for timestamps, it needs no knowledge of how many days the month has, and it never needs a 23:59:59 in it, which is the other common patch and is wrong by a second.

Build the boundary as a real date literal, DATE '2026-03-01', rather than a bare string, so the engine compares dates to dates instead of guessing.

5. Date arithmetic, and where two engines disagree

Subtracting two dates gives days, as a number:

SELECT DATE '2026-05-25' - DATE '2026-01-05';    140

Adding a period uses INTERVAL, and this is where it gets interesting. Ask two different engines for one month after 31 January:

DuckDB   SELECT DATE '2026-01-31' + INTERVAL 1 MONTH;      2026-02-28
SQLite   SELECT date('2026-01-31', '+1 month');            2026-03-03

Both are defensible and they are not the same answer. DuckDB clamps to the last valid day of the target month. SQLite adds one to the month number and then normalises 31 February forward into March. February 2026 has 28 days, so 31 February becomes 3 March.

Say out loud what that does to a report that adds one month to every contract start to get a renewal date. On thirty of the days in a month nothing happens. On the thirty-first, one engine renews at the end of February and the other renews in March, and if the same logic is implemented in two systems they will disagree for exactly those contracts.

The lesson is not that one engine is wrong. It is that month arithmetic at month ends is a genuine ambiguity, so if it matters in your data, test it on a 31st on the engine you are actually using, rather than assuming. That takes one query.

Two more that come up constantly:

SELECT DATE_TRUNC('month', DATE '2026-01-19');    2026-01-01    first of the month
SELECT LAST_DAY(DATE '2026-01-19');               2026-01-31    last of the month

6. The month that never becomes a row

Before the explanation: the data runs from 5 January to 25 May and the monthly query returned four rows. Say which month is missing and why.

April, because there are no April orders. GROUP BY can only produce a row for a value that exists in the data, so a month with nothing in it produces nothing. Asking for it directly confirms there is genuinely nothing there:

SELECT COUNT(*) AS rows_in_april, SUM(units * unit_price) AS april
FROM orders
WHERE order_date >= DATE '2026-04-01' AND order_date < DATE '2026-05-01';

0 | NULL

Note the NULL. SUM over no rows is not zero, it is unknown, which is the correct answer and is another thing to handle rather than to be annoyed by.

Now watch what the gap does to a growth calculation. Here is month over month with LAG, run on the four rows the data produced:

2026-01-01 | 2630 |   NULL
2026-02-01 | 2245 |  -14.6
2026-03-01 | 2585 |   15.1
2026-05-01 | 2430 |   -6.0

LAG means "the previous row", and the previous row for May is March. The −6.0 is a real comparison between two real months, two months apart, presented in a column headed month over month.

The fix is a calendar: generate every month in the period, then left join the data onto it, so a month with no orders arrives as a row with a zero.

WITH months AS (
  SELECT UNNEST(generate_series(DATE '2026-01-01', DATE '2026-05-01',
                                INTERVAL 1 MONTH)) AS month
),
by_month AS (
  SELECT DATE_TRUNC('month', order_date) AS month,
         SUM(units * unit_price)          AS revenue
  FROM orders GROUP BY 1
),
filled AS (
  SELECT m.month, COALESCE(b.revenue, 0) AS revenue
  FROM months m
  LEFT JOIN by_month b ON b.month = m.month
)
SELECT month, revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev,
       ROUND(100.0 * revenue
             / NULLIF(LAG(revenue) OVER (ORDER BY month), 0) - 100, 1) AS pct
FROM filled
ORDER BY month;

2026-01-01 | 2630 | NULL |   NULL
2026-02-01 | 2245 | 2630 |  -14.6
2026-03-01 | 2585 | 2245 |   15.1
2026-04-01 |    0 | 2585 | -100.0
2026-05-01 | 2430 |    0 |   NULL

Five rows now, and three of the numbers changed meaning. April is a visible zero and a true −100.0. May's percentage is NULL rather than −6.0, because NULLIF turned the zero denominator into a null and the division declined to answer, which is exactly right: growth from nothing has no percentage.

Three parts of that query are worth naming, because they are the pattern rather than the specifics. generate_series makes the calendar; the equivalent in Postgres is the same function without the UNNEST, and in engines without it you keep a small dates table. LEFT JOIN from the calendar to the data, never the other way round, so the calendar decides which rows exist. And COALESCE(..., 0) turns the resulting null into the zero you actually mean, deliberately, in one place.

The same arithmetic and the same trap in a spreadsheet is the Excel version of this page, and the LAG mechanics on their own are in month-over-month with LAG.

7. SQLite has no date type

This surprises people who learned SQL on SQLite, which is most beginners, so it is worth stating plainly. SQLite accepts DATE in a column definition and then stores whatever you give it. Asking what it actually holds:

SELECT typeof(order_date), order_date FROM orders LIMIT 1;

text | 2026-01-05

Text. The column was declared as DATE and the value is a string. Everything still works, as long as the strings are written YYYY-MM-DD, because that format sorts and compares correctly as text. Step outside it and the functions do not complain, they return nothing:

SELECT strftime('%Y-%m', '05/01/2026');    NULL

A null rather than an error, which will flow into a GROUP BY as a null group and sit there looking like a data problem rather than a format problem.

The rest of SQLite's date handling follows from having no type. Grouping needs strftime, differences need julianday, and both worked correctly on the same data:

SELECT strftime('%Y-%m', order_date), SUM(units*unit_price)
FROM orders GROUP BY 1 ORDER BY 1;
2026-01 | 2630   ...   2026-05 | 2430

SELECT julianday('2026-05-25') - julianday('2026-01-05');    140.0

So: on SQLite, store dates as YYYY-MM-DD strings and nothing else, and check the format on import rather than trusting it. On a typed engine, let the column be a date and let the engine enforce it. And when a query behaves oddly on one engine and correctly on another, the type system is the first place to look, not the last.

8. Sorting, and the month-name trap

Before the explanation: you format the month as a short name for the report, then order by it. Predict the order.

SELECT strftime(order_date, '%b') AS month, SUM(units*unit_price)
FROM orders GROUP BY 1 ORDER BY 1;

Feb | 2245
Jan | 2630
Mar | 2585
May | 2430

February first. The column is text, so the sort is alphabetical, and February beats January in the alphabet. Anyone reading the chart built on that will see a trend that never happened.

The fix is to keep the real date for ordering and use the label only for display:

SELECT strftime(m.month, '%b')  AS label,
       m.revenue
FROM ( SELECT DATE_TRUNC('month', order_date) AS month,
              SUM(units*unit_price)            AS revenue
       FROM orders GROUP BY 1 ) m
ORDER BY m.month;

Group and order by the date, format at the last possible moment. That single rule covers the month-name sort, the year-number merge from section three, and most of what goes wrong between a query and a chart.

Edge cases that break a date query

Six that get through.

Timestamps compared to dates. A timestamp column compared to DATE '2026-03-31' means midnight, so everything later that day is excluded. Half-open ranges make this impossible.

Time zones. A timestamp stored in UTC and read by somebody in another zone can move rows between days, which moves them between months at month boundaries. Decide which zone the report is in and convert once, explicitly.

A function on the filtered column. WHERE EXTRACT(YEAR FROM order_date) = 2026 stops most engines using an index on that column, and on a large table it turns a fast query into a slow one. Compare the raw column to a range instead.

Month number without year. Covered in section three, and worth repeating because it produces twelve tidy-looking rows out of three years of data.

Nulls in the date column. They form their own group and land at one end of the sort, so a report can carry a row with a blank month that nobody notices. Count them on purpose.

Weeks. DATE_TRUNC('week', ...) has to decide whether a week starts on Sunday or Monday, and engines differ. Check it once against a known date rather than assuming.