SQL Server Last Day of Month: EOMONTH, Previous and Next Month

Use EOMONTH. The SQL Server last day of month is one call, and a second argument shifts months:

SELECT EOMONTH('2026-03-14')     AS LastDay,   /* 2026-03-31 */
       EOMONTH('2026-03-14', -1) AS PrevMonth, /* 2026-02-28 */
       EOMONTH(GETDATE())        AS ThisMonth;

It returns a date, never a time. Tested on SQL Server 2025 Developer (17.0.1000.7). EOMONTH needs SQL Server 2012 or later.

EOMONTH Syntax: EOMONTH(start_date [, month_to_add])

start_date is any date type or date string; month_to_add is an integer offset. Leap-year February 2028 included:

SET NOCOUNT ON;
DECLARE @d datetime = '2026-03-14 15:45';
SELECT EOMONTH(@d)     AS ThisMonth,
       EOMONTH(@d, -1) AS PrevMonth,
       EOMONTH(@d, 1)  AS NextMonth,
       EOMONTH('2028-02-10') AS LeapFeb,
       CAST(SQL_VARIANT_PROPERTY(EOMONTH(@d), 'BaseType')
            AS varchar(8)) AS ReturnType;

SELECT EOMONTH(SYSUTCDATETIME()) AS ThisMonthUtc;

My run:

ThisMonth        PrevMonth        NextMonth        LeapFeb          ReturnType
---------------- ---------------- ---------------- ---------------- ----------
      2026-03-31       2026-02-28       2026-04-30       2028-02-29 date      
ThisMonthUtc    
----------------
      2026-11-30

The datetime input came back as date, so 15:45 is gone, and you don’t need any leap-year logic. The UTC row gave 11/30/2026; pass SYSUTCDATETIME() when the server clock isn’t UTC.

Variations: Previous Month, Next Month, Quarter and Year

You want	Query
Last day of previous month	EOMONTH(GETDATE(), -1)
Last day of next month	EOMONTH(GETDATE(), 1)
Last day of quarter	EOMONTH(DATEFROMPARTS(YEAR(@d), DATEPART(quarter, @d) * 3, 1))
Last day of year	DATEFROMPARTS(YEAR(@d), 12, 31)
First day of the month	DATEADD(day, 1, EOMONTH(@d, -1))

For 08/19/2026 the quarter row gave 09/30/2026 on my run. Start-of-month patterns live in the SQL Server first day of month guide.

Last Day of Month Without EOMONTH (SQL Server 2008 and DATETRUNC)

Pre-2012 code uses the DATEADD/DATEDIFF trick. DATETRUNC (SQL Server 2022+) reads better:

SET NOCOUNT ON;
DECLARE @d datetime = '2026-02-14 15:45';
SELECT DATEADD(day, -1,
         DATEADD(month, DATEDIFF(month, 0, @d) + 1, 0))
         AS OldWay,
       DATEADD(day, -1,
         DATEADD(month, 1, DATETRUNC(month, @d))) AS ViaDatetrunc,
       DAY(EOMONTH(@d)) AS DaysInMonth;

Same answer twice:

OldWay                  ViaDatetrunc            DaysInMonth
----------------------- ----------------------- -----------
2026-02-28 00:00:00.000 2026-02-28 00:00:00.000          28

Both return datetime midnight; DATETRUNC keeps the input type (see datetime vs datetime2). Don’t use the DATEADD(s, -1, ...) variant; you’ll miss the last second.

Why Does My EOMONTH Filter Miss Orders on the Last Day?

EOMONTH returns midnight, so on a datetime column BETWEEN ... AND EOMONTH(@d) stops at 03/31/2026 00:00. Sample table:

CREATE TABLE dbo.Orders (
    OrderId   int IDENTITY(1,1) PRIMARY KEY,
    Customer  nvarchar(15)  NOT NULL,
    Total     decimal(10,2) NOT NULL,
    OrderDate datetime      NOT NULL
);
INSERT INTO dbo.Orders (Customer, Total, OrderDate) VALUES
(N'Hannah Brooks', 120.00, '2026-02-27 09:15'),
(N'Tyler Reed',  89.99, '2026-02-28 16:40'),
(N'Grace Miller',  45.50, '2026-03-02 10:05'),
(N'Marcus Bennett', 310.00, '2026-03-18 13:30'),
(N'Kayla Parker',  72.25, '2026-03-31 00:00'),
(N'Jordan Cole',  18.75, '2026-03-31 14:20'),
(N'Ethan Price', 250.00, '2026-03-31 21:45'),
(N'Olivia Grant',  64.10, '2026-04-01 08:00');

Both filters for March:

SET NOCOUNT ON;
DECLARE @d date = '2026-03-14';
SELECT 'BETWEEN EOMONTH' AS Filter, COUNT(*) AS Orders,
       CAST(SUM(Total) AS decimal(10,2)) AS Sales
FROM dbo.Orders
WHERE OrderDate BETWEEN DATETRUNC(month, @d) AND EOMONTH(@d)
UNION ALL
SELECT '< EOMONTH + 1', COUNT(*), SUM(Total)
FROM dbo.Orders
WHERE OrderDate >= DATETRUNC(month, @d)
  AND OrderDate <  DATEADD(day, 1, EOMONTH(@d));

Counts:

Filter          Orders      Sales                                   
--------------- ----------- ----------------------------------------
BETWEEN EOMONTH           3                                   427.75
< EOMONTH + 1             5                                   696.50

BETWEEN lost two orders and $268.75. Use a half-open range, >= first day AND < DATEADD(day, 1, EOMONTH(@d)), and an index on OrderDate can still seek. On a date column BETWEEN is fine (see converting datetime to date).

Month-End Sales Report Grouped by EOMONTH

EOMONTH makes a clean month key for GROUP BY:

SET NOCOUNT ON;
SELECT EOMONTH(OrderDate) AS MonthEnd,
       COUNT(*)           AS Orders,
       CAST(SUM(Total) AS decimal(10,2)) AS Sales
FROM dbo.Orders
GROUP BY EOMONTH(OrderDate)
ORDER BY MonthEnd;

Report:

MonthEnd         Orders      Sales       
---------------- ----------- ------------
      2026-02-28           2       209.99
      2026-03-31           5       696.50
      2026-04-30           1        64.10

March: 5 orders, $696.50. For “March 2026” labels, get the month name from the date.

Last Business Day of the Month

Step back from Saturday or Sunday. I count days from 01/01/1900 (a Monday), so DATEFIRST can’t change it:

SET NOCOUNT ON;
SELECT m.MonthEnd,
       LEFT(DATENAME(weekday, m.MonthEnd), 3) AS Dow,
       DATEADD(day, CASE DATEDIFF(day, '19000101', m.MonthEnd) % 7
                      WHEN 5 THEN -1 WHEN 6 THEN -2 ELSE 0 END,
               m.MonthEnd) AS LastWeekday
FROM (VALUES (EOMONTH('2026-01-01')), (EOMONTH('2026-03-01')),
             (EOMONTH('2026-05-01')), (EOMONTH('2026-10-01'))) AS m(MonthEnd);

SET DATEFIRST 1;
SELECT DATEPART(weekday, '2026-05-31') AS SundayIsNow;

Weekdays:

MonthEnd         Dow LastWeekday     
---------------- --- ----------------
      2026-01-31 Sat       2026-01-30
      2026-03-31 Tue       2026-03-31
      2026-05-31 Sun       2026-05-29
      2026-10-31 Sat       2026-10-30
SundayIsNow
-----------
          7

Watch that 7. Under SET DATEFIRST 1 Sunday is day 7, so a DATEPART(weekday, ...) IN (1, 7) weekend test written for the US default flags Mondays and lets Saturdays through. Holidays need a calendar table.

Gotchas I Hit With EOMONTH

SET NOCOUNT ON;
DECLARE @r date;
BEGIN TRY SET @r = EOMONTH('9999-12-15', 1); END TRY
BEGIN CATCH PRINT CONCAT('Msg ', ERROR_NUMBER(), ': ', ERROR_MESSAGE()); END CATCH;

BEGIN TRY SET @r = EOMONTH('2026-02-30'); END TRY
BEGIN CATCH PRINT CONCAT('Msg ', ERROR_NUMBER(), ': ', ERROR_MESSAGE()); END CATCH;

SET @r = EOMONTH('2026-03-14', 1.9);
PRINT CONCAT('Offset 1.9 gives ', @r);

What sqlcmd printed:

Msg 517: Adding a value to a 'date' column caused an overflow.
Msg 241: Conversion failed when converting date and/or time from character string.
Offset 1.9 gives 2026-04-30

• Msg 517: an offset past 9999-12-31 overflows.
• Msg 241: ‘2026-02-30’ fails conversion before EOMONTH runs. See fixing the conversion failed error.
• Offset 1.9 became 1, silently.
• Compare with GETDATE() or CURRENT_TIMESTAMP gives the server’s month, not the user’s.

Copy-ready March filter:

DECLARE @d date = '2026-03-14';
SELECT OrderId, Customer, Total
FROM dbo.Orders
WHERE OrderDate >= DATEADD(day, 1, EOMONTH(@d, -1))
  AND OrderDate <  DATEADD(day, 1, EOMONTH(@d));

• More date math: SQL Server datetime functions
• Format the month-end date for reports
• On Postgres? PostgreSQL date_trunc

Frequently Asked Questions