Skip to content
Expert O'clock

SQL

SQL date functions for monthly reporting

Group dates into reporting months, filter with half-open ranges, handle missing periods, and avoid time-zone mistakes that shift totals.

Talha Ansari··5 min read

Key takeaways

  • Store and compare dates in a consistent type and time zone.
  • Truncate timestamps to the month before grouping.
  • Filter with a half-open range instead of BETWEEN.
  • Join to a calendar table so missing months appear.
On this page

Know what the date column stores

A date column and a timestamp column behave differently. A date has no time or zone. A timestamp may be stored with a zone, and the same moment can fall on different calendar days depending on the viewer. Confirm the type before writing a monthly report.

Also confirm which business event the date represents. Invoice date, payment date, order date, and delivery date can sit in different months for the same transaction. A revenue report grouped by the wrong date will reconcile to nothing.

Truncate to the reporting month

DATE_TRUNC sets a timestamp to the first moment of its month, quarter, or year. It is the standard way to group daily records into months. In PostgreSQL it returns a timestamp, so cast it to a date when the report should show a plain month. The examples use PostgreSQL syntax; other databases have equivalents with different names.

Group by the same expression you select. Some databases allow grouping by a column alias; others do not. Repeating the expression is portable and unambiguous, even if it looks slightly verbose.

Common monthly tasks in PostgreSQL
TaskExpression
First day of monthDATE_TRUNC('month', invoice_date)::date
First day of next month(DATE_TRUNC('month', invoice_date) + INTERVAL '1 month')::date
Last day of month(DATE_TRUNC('month', invoice_date) + INTERVAL '1 month' - INTERVAL '1 day')::date
Month labelTO_CHAR(invoice_date, 'YYYY-MM')

Filter with half-open ranges

BETWEEN includes both ends. With timestamps, that either misses records after midnight on the last day or forces an awkward 23:59:59 boundary that still misses fractional seconds. Use greater than or equal to the start and less than the start of the next period.

The query below reports the last twelve months including the current one. The start is the first day of the month eleven months ago; the end is the first day of next month. Every record in the window belongs to exactly one month.

Report the last twelve months with a half-open date rangeSQL
SELECT
  DATE_TRUNC('month', invoice_date)::date AS invoice_month,
  COUNT(*) AS invoices,
  SUM(amount) AS revenue
FROM invoices
WHERE invoice_date >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '11 months'
  AND invoice_date < DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month'
GROUP BY DATE_TRUNC('month', invoice_date)
ORDER BY invoice_month;

Handle month ends and missing months

Adding one month to 31 January can produce 28 or 29 February, depending on the database. For month-end logic, truncate to the first day, add a month, then subtract a day. That avoids ambiguous clamping rules.

A group-by query returns only months that have rows. A month with no invoices disappears rather than showing zero. For trend charts, generate the month list first and left join the data to it. Missing months then appear as zeros or nulls, and the difference is visible.

Map fiscal months explicitly

Fiscal calendars rarely match the calendar month. A fiscal year may start in April, July, or another month, and a period may be a four-week block rather than a calendar month. Do not encode that logic in a CASE expression that only one report uses.

Build or adopt a date dimension with one row per day and columns for calendar month, fiscal period, period start, period end, and whether the day is a working day. Every report then groups by the same definitions, and a change is made in one place.

  • Keep the fiscal mapping in a table, not in the report query.
  • Store period start and end dates so half-open filters still work.
  • Label periods with the fiscal year and period number used by finance.
  • Test a leap year and a year with an unusual period pattern.

Check the calendar before publishing

Compare the number of months in the result with the number expected. Check the first and last month boundaries, especially when the report uses the current date. A report run on the first day of a month can include a nearly empty current month.

Reconcile the monthly total with an independent total for the same window. If they differ, look for a time-zone conversion, a filter on the wrong date column, or records with null dates.

  • Count distinct months and compare with the requested window.
  • Check for null or future dates outside the expected range.
  • Confirm whether the current month is complete or partial.
  • Run the report for a closed month and compare with the ledger.

Frequently asked questions

Is DATE_TRUNC or EXTRACT better for monthly reports?+

DATE_TRUNC keeps a sortable date and groups correctly across years. EXTRACT returns a number, so January of different years can collapse together unless the year is also included.

Why do some records fall outside a date filter?+

The filtered column may be a timestamp in another time zone, or the report may filter the wrong date, such as order date instead of invoice date.

How are months with no activity shown?+

Generate a month list with a calendar table or a series, then left join the activity to it and replace nulls with zeros.

Is BETWEEN safe for dates?+

It is safe for date columns when both ends are dates you intend to include. It is risky for timestamps, where the end boundary is rarely the last instant of the day.

Need help applying this?

Improve your data cleanup & pipelines.

Review the service scope or tell us what you are trying to improve. We will reply with a clear next step.

Discuss this serviceView service
Free KPI definition template

Related reading