Level 8 · Debug: January looks twice as big

This query runs, the chart renders, and every number is wrong. The tell is in the ticket: 12 bars for a two-year range.

Spotting the bug

EXTRACT(month FROM order_date) returns a number from 1 to 12 and nothing else. Grouping by it puts every January in the data into one bucket. With 2024 and 2025 in the filter, the January bar is January 2024 (213 completed orders) plus January 2025 (268): 481.

Two quick checks catch this kind of bug:

1. Row count. A monthly series over two years has 24 rows. Twelve means years were merged.
2. Compare one number with a trusted source. Finance’s January 2025 figure did not match.

The fix: truncate, don’t extract

DATE_TRUNC('month', ts) keeps the year and month and sets the rest to the start of the month:

SELECT CAST(DATE_TRUNC('month', order_date) AS DATE) AS month,
       COUNT(*) AS orders
FROM orders
WHERE status = 'completed'
  AND EXTRACT(month FROM order_date) = 1
GROUP BY CAST(DATE_TRUNC('month', order_date) AS DATE)
ORDER BY month;

This returns 178, 213 and 268 completed January orders for 2023, 2024 and 2025. Notice that EXTRACT is fine in the WHERE clause here: picking Januaries from every year is exactly what it is for.

A real DATE in the month column also helps whoever consumes the result. Charting tools put dates on a proper time axis, and a later join to another monthly table (marketing spend, targets) can match on the date directly instead of on a year and a month number.

EXTRACT(month) does have honest uses: a seasonality view that averages “all Januaries” on purpose. In that case divide by the number of years, and label the axis “month of year”, not a date.

Common mistakes

• Grouping by EXTRACT(year ...) and EXTRACT(month ...) as two columns works, but returns two numbers instead of one date, which charts and joins handle less well.
• Forgetting the cast: DATE_TRUNC on a timestamp returns a timestamp such as 2025-01-01 00:00:00.

In SQL Server

DATETRUNC(month, order_date) exists from SQL Server 2022. On older versions use DATEFROMPARTS(YEAR(order_date), MONTH(order_date), 1). MONTH(order_date) and DATEPART(month, order_date) behave like EXTRACT: month number only.