EOMONTH: Month Ends Are Different From DATEADD Month Clipping

EOMONTH gives me a month-end date when that is the actual reporting requirement. DATEADD moves a date by a month. These operations can agree for one step and disagree on the next.

January 31 makes that difference easy to see. February has no matching day, so DATEADD clips the result to February’s last day. A later addition starts from that clipped date.

Compare complete date values

The query contains four explicitly typed dates. Two start on January 31 in different years. Another starts on leap day, and the final date starts in the middle of March.

CONVERT uses style 112 for the eight-digit date text. Every later expression receives a date value rather than an ambiguous date string. That also makes the DATEADD result a date in this example.

I return both repeated one-month additions and one two-month addition. Those columns answer different questions after a clipped intermediate date. Keeping them together exposes the rule before it enters a billing schedule.

WITH Dates AS
(
    SELECT CaseId, CONVERT(date, DateText, 112) AS StartDate
    FROM (VALUES
        (1, '20240131'),
        (2, '20230131'),
        (3, '20240229'),
        (4, '20240315')
    ) AS v(CaseId, DateText)
)
SELECT CaseId, StartDate,
       EOMONTH(StartDate) AS CurrentMonthEnd,
       EOMONTH(StartDate, 1) AS NextMonthEnd,
       DATEADD(month, 1, StartDate) AS AddedOneMonth,
       DATEADD(month, 1, DATEADD(month, 1, StartDate)) AS TwoSeparateAdds,
       DATEADD(month, 2, StartDate) AS OneTwoMonthAdd,
       EOMONTH(StartDate, 2) AS SecondMonthEnd
FROM Dates
ORDER BY CaseId;

Why the January rows diverge

For January 31, 2024, the expected one-month result is February 29. A second separate addition then produces March 29. It uses the day from the intermediate February date.

Adding two months directly to that January date produces March 31. March has a matching day, so this calculation does not need February’s clipped value. Repeated additions and a single combined addition are therefore different here.

The nonleap January row follows the same pattern with February 28. Its repeated addition produces March 28, while the direct two-month addition produces March 31. The difference comes from the calendar and the chosen operation.

EOMONTH with an offset of two returns March 31 for both January rows. It asks for the final day of that target month. It doesn’t carry forward the clipped day from February.

Choose the business rule first

The leap-day row separates the rules again. DATEADD moves February 29 to March 29. EOMONTH with an offset of one returns March 31 instead.

For March 15, adding a month gives April 15. The next month-end is April 30. Neither value is automatically correct for a contract; the schedule must define which date it wants.

I keep a stable anchor when a schedule follows an original contractual day. Repeatedly updating the last generated date can carry a clipped day into later months. Generate offsets from the anchor when that matches the intended rule.

For month-end reporting, calculate each target month-end directly. This keeps February’s length from becoming a permanent day-of-month choice. It also makes the query’s intent visible to someone reading the schedule later.

Keep the example within its limits

EOMONTH returns date and is available in SQL Server 2012 and later. Its offset can fail if the target date exceeds the supported range. This bounded example stays far away from those limits.

These values have no time of day or time-zone conversion. A timestamp filter still needs its own boundary rule. For whole-day ranges, define the next boundary carefully rather than inventing a final fractional second.

The query above includes every date shown here. Compare all columns when adapting it, especially the two different ways of adding two months. A single successful January-to-February check cannot establish the complete schedule behavior.

Choose a month-end rule or an anchored day rule, then calculate dates consistently.

A month-end is not a month added, it is a rule you choose on purpose.

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