MONTH, DATEPART and DATENAME: Getting Month Names and Numbers Right

A month has a number, a name, and a place inside a particular year. DATENAME supplies the label, but reliable reporting keeps that label separate from the value used for grouping and sorting.

Choose a Numeric Value or a Display Label

MONTH returns the integer month number. DATEPART with the month argument returns that numeric component too. The month-name function returns text whose interpretation belongs to presentation rather than arithmetic. Decide whether the consumer needs a key, a grouping boundary, or a readable label before choosing the expression.

DECLARE @ReportDate date=DATEFROMPARTS(2026,9,20);
SELECT MONTH(@ReportDate) AS MonthNumber,
       DATEPART(MONTH,@ReportDate) AS MonthPart,
       DATENAME(MONTH,@ReportDate) AS MonthLabel;

A numeric result is convenient for a calendar ordering rule, but month number alone does not identify a complete reporting period. September in one year and September in another share that number. Preserve the year or a month-start date whenever the report crosses year boundaries.

I keep the period key in the result even when the screen displays only a friendly name. That makes sorting, reconciliation, and downstream exports more reliable. A localized label should not become the only available identity for a financial or operational period merely because it looks pleasant in the heading.

Control the Session Language Behind DATENAME

The label depends on the session language. Login defaults and SET LANGUAGE can change it, so a query that works in one connection can display different names in another. The following example uses a typed date and restores the previous session language afterward.

DECLARE @OriginalLanguage sysname=@@LANGUAGE;
DECLARE @ReportDate date=DATEFROMPARTS(2026,9,20);
SET LANGUAGE us_english;
SELECT DATENAME(MONTH,@ReportDate) AS EnglishMonth;
SET LANGUAGE French;
SELECT DATENAME(MONTH,@ReportDate) AS FrenchMonth;
SET LANGUAGE @OriginalLanguage;

Typed dates avoid ambiguity from language-sensitive date strings. Session language also affects other behavior, so changing it solely for a label needs care in a shared connection. Restore the accepted state and test the application connection's initialization rules. A month report should not quietly alter how a later ambiguous date literal is interpreted.

Use DATENAME when the intended label should follow the chosen session language. If the application requires a fixed culture independent of that session, make the presentation rule explicit instead. The distinction is between two useful contracts, not a competition over which function is more modern.

Use FORMAT Instead of DATENAME for a Fixed Culture

FORMAT accepts an explicit culture for a readable label. Use it when culture-specific presentation belongs in the SQL output and the supported environment permits its CLR-based implementation. The culture argument is a deliberate display choice, not a replacement for the stored date or period identity.

DECLARE @ReportDate date=DATEFROMPARTS(2026,9,20);
SELECT FORMAT(@ReportDate,N'MMMM',N'en-US') AS EnglishLabel,
       FORMAT(@ReportDate,N'MMMM',N'fr-FR') AS FrenchLabel,
       FORMAT(@ReportDate,N'yyyy-MM',N'en-US') AS DisplayPeriod;

The function is nondeterministic and has different execution considerations from simple date-part extraction. Avoid adding it to every source row in a large aggregate when formatting the small final result would suffice. Measure the actual query shape rather than assuming that a convenient label function is free.

Keep unsupported or invalid culture handling visible. A failed formatting request should not silently change the report to an unrelated language. Where the application already manages localization, return a stable period key and let that presentation layer choose its own accepted labels.

Sort DATENAME Labels by Calendar Position

Alphabetical order is not calendar order. Carry a month number beside each name and sort by the number. The following temporary calendar uses explicit numeric inputs and captures English labels under a deliberate session language. It does not depend on the connection's prior language default.

CREATE TABLE #CalendarMonths
(
    MonthNumber int NOT NULL PRIMARY KEY,
    MonthLabel nvarchar(30) NOT NULL
);
DECLARE @OriginalLanguage sysname=@@LANGUAGE;
SET LANGUAGE us_english;
INSERT #CalendarMonths (MonthNumber,MonthLabel)
SELECT n.MonthNumber,DATENAME(MONTH,DATEFROMPARTS(2026,n.MonthNumber,1))
FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) AS n(MonthNumber);
SET LANGUAGE @OriginalLanguage;
SELECT MonthNumber,MonthLabel FROM #CalendarMonths ORDER BY MonthNumber;

Retain the numeric sort column in exports and chart inputs. Some consumers re-sort a text label automatically, undoing the database's intended order. Verify that behavior in the final report. A calendar sorted alphabetically has followed its instructions perfectly and still made the reader do unnecessary work.

Create Sales Inputs Across Different Years

Use a dataset that includes the same month in more than one year when testing aggregation. A single-year sample cannot reveal accidental year mixing. Store dates as typed values and amounts with an accepted exact numeric type, rather than relying on formatted strings as the underlying report data.

CREATE TABLE #MonthlySales
(
    SaleID int NOT NULL PRIMARY KEY,
    SaleDate date NOT NULL,
    Amount decimal(12,2) NOT NULL
);
INSERT #MonthlySales VALUES
(1,DATEFROMPARTS(2025,1,10),100.00),
(2,DATEFROMPARTS(2026,1,12),200.00),
(3,DATEFROMPARTS(2026,2,14),150.00),
(4,DATEFROMPARTS(2026,2,20),50.00);

These are synthetic inputs for checking the grouping contract. They are not actual sales results or an observed reporting incident. Add boundary dates and any supported NULL policy to the rehearsal population. The goal is to expose distinctions the report must preserve before it reaches a larger real dataset.

Group by a Complete Month Boundary

A month-start date gives each year-and-month combination a sortable identity. Group using that identity, then format or name the final period for display. This prevents separate January populations from being combined solely because both dates return month number one or the same month label.

WITH PeriodRows AS
(
    SELECT DATEFROMPARTS(YEAR(SaleDate),MONTH(SaleDate),1) AS MonthStart,Amount
    FROM #MonthlySales
)
SELECT MonthStart,YEAR(MonthStart) AS ReportYear,
       MONTH(MonthStart) AS ReportMonth,SUM(Amount) AS TotalAmount
FROM PeriodRows
GROUP BY MonthStart
ORDER BY MonthStart;

Alternatively, group by both YEAR and MONTH explicitly. Keep both values through the output and use them together for ordering. Fiscal periods need an approved calendar mapping instead of assuming calendar-month boundaries describe the organization's reporting convention.

Filter Dates With Clear Boundaries

When restricting the source to one month, use an inclusive start and an exclusive next-month boundary. Avoid applying MONTH to every source date in the WHERE clause merely to select one period. That expression also needs a year restriction and can complicate use of an ordinary date-leading access path.

DECLARE @MonthStart date=DATEFROMPARTS(2026,2,1);
SELECT SaleID,SaleDate,Amount
FROM #MonthlySales
WHERE SaleDate>=@MonthStart AND SaleDate<DATEADD(MONTH,1,@MonthStart);

I verify the period boundary before reviewing its displayed name. For timestamp data, define the accepted time zone before translating local monthly boundaries to the stored time convention. A correct-looking label cannot reveal that an event near midnight was grouped into the wrong local period.

Validate the Consumer's Period Contract

Which value should the next system use to identify and sort this period? Include that key with the friendly label. Test different session languages, multiple years, year transitions, and the consumer's ordering behavior. Keep totals tied to typed dates rather than localized text.

DATENAME is useful for readable output when its language contract is intentional. Preserve numeric and full-period keys alongside it, and the report can change its displayed language without changing which rows belong to a month.

Related reading on this blog: Weekday Logic That Works Under Any DATEFIRST Setting and Date Boundaries With DATETRUNC and EOMONTH in SQL Server 2022.

A month name is not a reporting-period identity, it is a display label that needs a date or numeric key beside it.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.