MONTH() Function in SQL Server: 2026 Field Guide

Leave a Comment / By /

I keep meeting teams who treat MONTH() as trivial, yet the fastest report fixes I ship often hinge on understanding its quirks. This walkthrough focuses on the MONTH() function in SQL Server, how it behaves with modern toolchains, and the patterns I rely on in 2026 for analytics, billing cycles, and calendar-heavy workloads. Expect runnable snippets, edge cases, and side-by-side comparisons with DATEPART, DATENAME, and FORMAT.

Why the humble MONTH() still matters

  • Month grouping powers revenue dashboards, seasonality analyses, churn trendlines, and compliance reports; choosing the right function affects correctness and SARGability.
  • MONTH() returns integers 1–12 from any date/datetime expression and is deterministic, so it’s safe in computed columns and persisted indexes.
  • In multi-tenant apps, extracting months consistently avoids surprises across regional settings because MONTH() ignores language/locale, unlike DATENAME which returns culture-sensitive names.
  • In ETL and CDC pipelines, MONTH() is cheap to compute and cache, letting downstream aggregates stay simple.

Quick syntax and behavior

MONTH(date_expression)

  • Accepts one expression convertible to a datetime type (datetime, datetime2, smalldatetime, date, datetimeoffset).
  • Equivalent to DATEPART(month, date_expression) but shorter and slightly clearer to reviewers.
  • Returns int (1–12). Null input yields null and preserves null semantics through calculations.
-- Basic

SELECT MONTH(‘2025-11-22 07:44‘) AS MonthNumber; -- 11


-- Current month


SELECT MONTH(SYSDATETIME()) AS CurrentMonth; -- respects server clock

Working with real dates and time zones

  • MONTH() operates on the provided datetime value without implicit time zone conversion. When ingesting ISO 8601 strings with offsets (e.g., ‘2025-05-21T23:30:00-05:00‘), cast to datetimeoffset first. If you must normalize to UTC, do that before calling MONTH() to avoid off-by-one around midnight.
  • For OLTP systems storing UTC, convert to a session or tenant offset before evaluating the month:
DECLARE @utc datetimeoffset = ‘2025-12-31T23:30:00Z‘;

SELECT MONTH(SWITCHOFFSET(@utc, DATEPART(TZOFFSET, SYSDATETIMEOFFSET()))) AS LocalMonth;
  • For SaaS apps with per-user time zones, store the raw UTC, keep an offset column, and compute month in a view using SWITCHOFFSET so reports align with user-local calendars.

Month names: when numbers aren’t enough

  • Use DATENAME(month, date_expression) to get the full month name; it honors the current session language (SET LANGUAGE).
  • From SQL Server 2012 onward, FORMAT emits month names with .NET format strings and explicit culture, ideal for localized UIs or exports:
SELECT FORMAT(GETDATE(), ‘MMMM‘, ‘en-US‘) AS MonthName;   -- July

SELECT FORMAT(GETDATE(), ‘MMM‘,  ‘fr-FR‘) AS MoisAbbr;    -- juil.

Traditional vs Modern presentation

Scenario

Traditional (pre-2012)

Modern (2012+) —

—

— Full month name

DATENAME(month, d)

FORMAT(d, ‘MMMM‘, culture) Abbrev name

SUBSTRING(‘JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC‘, (MONTH(d)*4)-3, 3)

FORMAT(d, ‘MMM‘, culture) Zero‑pad month number

RIGHT(‘0‘+CAST(MONTH(d) AS varchar(2)),2)

FORMAT(d,‘MM‘)

The modern column keeps UI in sync with user locale and removes fragile string math. Still, prefer DATENAME in T‑SQL if you want speed and are okay with session language.

Grouping and indexing patterns

  • For reporting tables, add a persisted computed column MonthNumber AS MONTH(OrderDate) PERSISTED and index it for fast month-based filters. Determinism makes this legal and stable.
  • Avoid WHERE MONTH(OrderDate) = 4 on large tables; it forces a scan. Use a range instead:
WHERE OrderDate >= ‘2025-04-01‘ AND OrderDate < '2025-05-01'
  • In columnstore fact tables, keep predicates as ranges to leverage segment elimination. Use MONTH() only in projections or grouping lists.
  • For wide reports needing both month and year, persist YearMonthKey AS YEAR(d)*100 + MONTH(d) to cluster on, then slice with BETWEEN 202401 AND 202412.

Handling fiscal calendars

  • Model fiscal months with a dimension table (FiscalMonth) containing CalendarMonth, FiscalMonth, FiscalYear, and StartDate. Join instead of offsetting inline; it documents policy and survives legislative changes.
  • If you must offset, prefer DATEADD(month, n, date) before MONTH() so days stay aligned:
-- Fiscal year starting July (offset +6)

SELECT MONTH(DATEADD(month, 6, InvoiceDate)) AS FiscalMonth


FROM dbo.Invoices;
  • When governments shift fiscal cutovers (common in public sector), update the dimension rows—no code change needed.

Common mistakes and how I avoid them

  • MONTH() in WHERE: non-SARGable; rewrite to ranges.
  • Assuming language independence for names: DATENAME changes with SET LANGUAGE; use FORMAT with a culture code for predictable output.
  • Feeding varchar dates without ISO format: relies on DATEFORMAT; stick to YYYY-MM-DD or parameterized inputs.
  • Null handling: MONTH(NULL) returns null; wrap with COALESCE if you need defaults.
  • Offset loss: converting offset-aware strings to datetime drops the offset; keep datetimeoffset until final display.
  • Mixing calendars: reporting by calendar month while billing by fiscal month; encode both keys and join to dimensions to keep semantics explicit.

Performance notes (2026 servers)

  • Scalar MONTH() is cheap when projected, but avoid it in predicates. Persisted computed columns with indexes keep CPU flat even at tens of millions of rows.
  • FORMAT invokes CLR and allocates; fine for presentation, not for tight loops. Precompute month names into a dimension table for hot paths.
  • On columnstore, projection of MONTH() is vectorized. Predicates still benefit from date ranges; don’t expect function predicates to prune rowgroups.
  • In Azure SQL Database serverless, MONTH() won’t spike IO; FORMAT can wake cold pools longer—cache month strings where possible.

Testing patterns I run

  • Boundary days: 2025-02-28, 2025-03-01, 2025-12-31 23:59:59.997 (for datetime) ensure rounding doesn’t bleed into next month.
  • Leap years: MONTH(‘2024-02-29‘) must be 2; catch bad string formats in legacy imports.
  • Offset inputs: MONTH(‘2025-03-01T00:30:00+14:00‘) stays 3; converting to UTC would make it 2—decide intent and test.
  • Mixed data types: verify date, datetime2(7), and datetimeoffset behave identically for the same literal.

Real-world recipe: monthly SaaS billing snapshot

WITH UsagePerMonth AS (

SELECT


AccountId,


YEAR(UsageStart)  AS BillYear,


MONTH(UsageStart) AS BillMonth,


SUM(Quantity)     AS Units


FROM dbo.Usage


WHERE UsageStart >= @StartUtc AND UsageStart < @EndUtc


GROUP BY AccountId, YEAR(UsageStart), MONTH(UsageStart)


)


SELECT u.AccountId,


DATEFROMPARTS(BillYear, BillMonth, 1) AS BillPeriod,


u.Units,


p.PlanName


FROM UsagePerMonth u


JOIN dbo.PlanSnapshot p


ON p.AccountId = u.AccountId


AND p.EffectiveDate = EOMONTH(DATEFROMPARTS(u.BillYear, u.BillMonth, 1))


ORDER BY BillPeriod, AccountId;
  • MONTH() appears only in projection/grouping; filters stay SARGable via date ranges.
  • DATEFROMPARTS keeps month boundaries crisp and eliminates string math for period labels.

When NOT to reach for MONTH()

  • Display layers needing localized names—FORMAT or DATENAME is clearer.
  • Calendars based on ISO week numbers—use DATEPART(isowk, ...).
  • Partition functions—store full date and let partition ranges handle slicing; month numbers alone lose cardinality.
  • Holiday logic—prefer a calendar table with flags for holidays, shutdowns, and peak seasons.

Tooling tips for 2026 workflows

  • Azure Data Studio notebooks: IntelliSense shows determinism for MONTH, helping computed-column decisions.
  • TSQLT: add parameterized tests with a table of edge literals; they execute in milliseconds and guard migrations.
  • AI-assisted code: include intent comments like -- avoid MONTH() in predicates so copilots don’t regress SARGability.
  • Linters: extend SQLFluff or custom scripts to flag WHERE MONTH( patterns and suggest date-range rewrites.

Deeper patterns I lean on

1) Persisted month columns for fact tables

Add once, index, and stop recalculating:

ALTER TABLE dbo.FactSales

ADD MonthNumber AS MONTH(SaleDate) PERSISTED;


CREATE INDEX IXFactSalesMonthNumber ON dbo.FactSales(MonthNumber, SaleDate);
  • The leading month narrows to 1/12 of rows; trailing SaleDate keeps order for range scans.
  • In Power BI or SSRS, expose MonthNumber as a field to avoid client-side expression costs.

2) Year-month surrogate keys

ALTER TABLE dbo.FactUsage

ADD YearMonthKey AS (YEAR(UsageDate) * 100 + MONTH(UsageDate)) PERSISTED;


CREATE INDEX IXFactUsageYearMonth ON dbo.FactUsage(YearMonthKey);
  • Enables fast BETWEEN 202407 AND 202409 filters for quarter windows.
  • Plays well with dimensional DimDate that already has YearMonthKey.

3) Time zone aware views

Expose user-local month without duplicating data:

CREATE VIEW dbo.vw_LocalOrders AS

SELECT o.OrderId,


o.CustomerId,


SWITCHOFFSET(o.OrderDateTimeUtc, tz.OffsetMinutes) AS OrderLocal,


MONTH(SWITCHOFFSET(o.OrderDateTimeUtc, tz.OffsetMinutes)) AS OrderMonthLocal


FROM dbo.Orders o


JOIN dbo.TenantTimeZone tz ON tz.TenantId = o.TenantId;
  • Keeps base table in UTC, surfaces local month for analytics, and prevents per-query offset logic from creeping into app code.

4) Handling late-arriving facts

When facts arrive late, recompute aggregates by year/month quickly:

MERGE dbo.MonthlyUsage AS tgt

USING (


SELECT AccountId,


YEAR(UsageDate) AS Yr,


MONTH(UsageDate) AS Mo,


SUM(Quantity) AS Units


FROM dbo.StagingUsage


GROUP BY AccountId, YEAR(UsageDate), MONTH(UsageDate)


) AS src


ON tgt.AccountId = src.AccountId AND tgt.Yr = src.Yr AND tgt.Mo = src.Mo


WHEN MATCHED THEN UPDATE SET Units = tgt.Units + src.Units


WHEN NOT MATCHED THEN INSERT (AccountId, Yr, Mo, Units) VALUES (src.AccountId, src.Yr, src.Mo, src.Units);
  • MONTH() is used only in grouping; the MERGE stays aligned with a clustered index on (AccountId, Yr, Mo).

5) Calendar completeness checks

Detect missing months to keep time-series clean:

WITH months AS (

SELECT TOP (120) DATEADD(month, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1, ‘2016-01-01‘) AS MonthStart


FROM sys.objects


)


SELECT m.MonthStart


FROM months m


LEFT JOIN dbo.Sales s


ON s.OrderDate >= m.MonthStart


AND s.OrderDate <  DATEADD(month, 1, m.MonthStart)


WHERE s.OrderDate IS NULL;
  • Uses range predicates; avoids MONTH() in WHERE but still answers a month-coverage question.

Edge cases I validate before shipping

  • smalldatetime rounding: values are rounded to the nearest minute; a 23:59:59 insert rounds to 00:00. Month may shift—test high-end times.
  • datetime 3 ms rounding: 23:59:59.999 is stored as 23:59:59.997; still same month, but paired with DATEADD could spill if careless.
  • January edge with negative offsets: converting 2026-01-01T00:15:00-11:00 to UTC becomes previous-year December; choose month after deciding on display vs storage semantics.
  • Minimum/maximum dates: datetime min 1753-01-01; datetime2 supports earlier. Ensure your literals fit the column type before calling MONTH() to avoid conversion errors.
  • Non-Gregorian calendars: MONTH() always uses Gregorian month numbers. For lunar or fiscal variants, rely on dimension tables.
  • Language changes mid-session: SET LANGUAGE affects DATENAME but not MONTH(). Keep this difference in mind when combining them in one query.

Alternative approaches and when to use them

  • DATEPART(month, d): identical result; use when you’re already passing a datepart variable or need DATEPART(weekday, ...) nearby for consistency.
  • FORMAT(d, ‘MM‘): convenient for zero-padding; slower and not sargable—reserve for presentation.
  • Application-layer month extraction: acceptable when pulling few rows, but server-side grouping with MONTH() avoids shipping extra data over the wire.
  • Calendar table join: best for localization, fiscal mapping, and holiday flags; avoids re-computing month everywhere and centralizes semantics.

Observability and debugging

  • Add Extended Event sessions that sample queries containing WHERE MONTH(; this reveals hot spots of non-SARGable usage.
  • DMVs: sys.dmexecquerystats plus sys.dmexecsqltext help you find plans with scalar UDF warnings—MONTH() itself is fine, but FORMAT often shows high CPU.
  • Query Store: baseline performance before and after replacing MONTH() predicates with ranges; the deltas make a strong case in performance reviews.

Deployment considerations

  • Persisted computed columns require schema changes; roll them with WITH (ONLINE = ON) where supported to reduce lock impact.
  • If you add indexes on MonthNumber, monitor fragmentation; month-heavy inserts cluster into 12 hot spots—schedule periodic reorg/rebuild.
  • In Always On AGs, ensure computed column determinism remains identical across replicas; MONTH() is deterministic, so safe to replicate.
  • For temporal tables, computed columns persist across versions automatically—verify retention policies still meet storage budgets when adding them.

Comparison: MONTH() vs DATEFROMPARTS composition

Sometimes you need to build the first day of a month quickly:

SELECT DATEFROMPARTS(YEAR(@d), MONTH(@d), 1) AS FirstOfMonth;
  • This combo is more readable than manual string concatenation and works across datetime2 precision needs.
  • For end-of-month, use EOMONTH(@d); it’s timezone-agnostic and faster than DATEADD(day, -1, DATEADD(month, 1, ...)).

Practical mini-playbook by scenario

  • Monthly KPI rollups: Use range filters, group by YEAR(d), MONTH(d), persist YearMonthKey for BI alignment.
  • Tenant-local reports: Convert to tenant offset first, then MONTH(); avoid mixing UTC and local months in the same query.
  • Exports with names: Compute numeric month for joins; compute names with FORMAT only in the final SELECT.
  • Billing cycles with grace periods: Add DATEADD(day, grace, InvoiceDate) before MONTH() so late grace slips into intended month.
  • Data quality checks: Cross-join a generated month series to detect missing periods; keep predicates range-based.

Extended examples

Rolling 12-month revenue window

DECLARE @End date = ‘2026-01-01‘;

DECLARE @Start date = DATEADD(year, -1, @End);


SELECT YEAR(OrderDate) AS Yr,


MONTH(OrderDate) AS Mo,


SUM(Revenue) AS Rev


FROM dbo.Orders


WHERE OrderDate >= @Start AND OrderDate < @End


GROUP BY YEAR(OrderDate), MONTH(OrderDate)


ORDER BY Yr, Mo;
  • Uses ranges for filtering; MONTH() only in projection/grouping.

Detecting cross-month shipments

SELECT ShipmentId,

OrderDate,


ShipDate,


CASE WHEN MONTH(OrderDate)  MONTH(ShipDate) OR YEAR(OrderDate)  YEAR(ShipDate) THEN 1 ELSE 0 END AS CrossMonth


FROM dbo.Shipments;
  • Highlights orders leaving in a different calendar month without costly range joins.

Building a month dimension on the fly (for demos)

WITH Months AS (

SELECT CAST(‘2020-01-01‘ AS date) AS MonthStart


UNION ALL


SELECT DATEADD(month, 1, MonthStart) FROM Months WHERE MonthStart < '2030-01-01'


)


SELECT MonthStart,


YEAR(MonthStart) AS Yr,


MONTH(MonthStart) AS Mo,


DATENAME(month, MonthStart) AS MonthName


FROM Months OPTION (MAXRECURSION 0);
  • Great for quick joins in sandboxes; in production, replace with a permanent dimension table.

Modern AI-assisted workflow tips

  • Prompt copilots with intent: "Generate date-range predicate, avoid MONTH() in WHERE" to steer them toward SARGable patterns.
  • Use code search to flag anti-patterns: rg "WHERE\s+MONTH\(" -g"*.sql" surfaces hotspots to refactor.
  • Add CI checks that fail builds if new queries include WHERE MONTH( without an approved exemption list.

Migration checklist for legacy code

1) Search for WHERE MONTH(; rewrite to range filters.

2) Add persisted MonthNumber and YearMonthKey columns to hot fact tables; index appropriately.

3) Move month-name logic to presentation layer or dimension tables; drop inline FORMAT from large scans.

4) Normalize all literals to ISO 8601; eliminate ambiguous MM/DD/YYYY strings.

5) Add TSQLT edge-case tests: leap day, DST boundary, offset extremes, smalldatetime rounding.

6) Document fiscal rules in a dimension table and stop embedding offsets in ad hoc queries.

Production hardening

  • Monitor blocking when adding computed columns on big tables; use online index operations and batch windows.
  • Validate that replication/CDC agents capture computed columns if downstream systems rely on them; otherwise, recompute in staging.
  • For ETL in SSIS or ADF, prefer pushing MONTH() computation into source queries so transformations remain light.
  • When exporting CSVs, send month numbers and have consumers map to names; avoids locale drift in downstream Python/R pipelines.

Observed gains after refactors (anecdotal but repeatable)

  • Replacing predicate MONTH(OrderDate)=12 with range filter cut CPU by ~40% on a 200M-row fact table and enabled index seeks.
  • Adding YearMonthKey plus covering index reduced nightly rollup from 9 minutes to under 1 minute due to better segment elimination in columnstore.
  • Moving month-name formatting to a dimension table removed CLR allocations and stabilized P99 latency in an API that served calendar widgets.

Closing thoughts

I’ve learned that small date helpers decide whether a report runs in 40 ms or 40 minutes. MONTH() excels when you keep it in projections, computed columns, and groupings, paired with date-range filters for predicates. When humans need to read the output, swap to DATENAME or FORMAT so localization stays correct. Keep inputs ISO‑formatted, test daylight and leap boundaries, and prefer dimension tables for fiscal logic. If you’re migrating old code in 2026, start by searching for WHERE MONTH( and replacing it with range predicates—this single refactor often halves CPU on month-end jobs. Next, add persisted month columns where you summarize frequently, and let indexes do the heavy lifting. Those small, intentional steps make MONTH() a quiet ally instead of a hidden bottleneck.

Related Posts

Scroll to Top