EOMONTH in SQL Server: last and first day of the month

October 8, 2016 · updated November 4, 2026

EOMONTH (end of month) returns the last day of the month. SQL Server has no built-in beginning-of-month function like SOMONTH, so for the first day I use DATEFROMPARTS (or DATETRUNC on SQL Server 2022+):

DECLARE @d datetime = '2026-11-04T15:30:00';

SELECT EOMONTH(@d);                           -- 2026-11-30
SELECT DATEFROMPARTS(YEAR(@d), MONTH(@d), 1); -- 2026-11-01

EOMONTH function syntax

EOMONTH ( start_date [ , month_to_add ] )
  • start_date — a date, datetime, datetime2 or a string that converts to a date.
  • month_to_add — optional integer. EOMONTH shifts the date by that many months first, then takes the last day. Negative values go back.
  • The result is always date, whatever type you pass in. The time part is dropped.

EOMONTH appeared in SQL Server 2012 and is available in Azure SQL Database, Azure SQL Managed Instance, Azure Synapse and Microsoft Fabric. On 2008 and older the replacement is DATEADD(day, -1, DATEADD(month, DATEDIFF(month, 0, @d) + 1, 0)), which returns datetime.

Last day of the current, previous and next month

SELECT EOMONTH(GETDATE())     AS this_month,  -- 2026-11-30
       EOMONTH(GETDATE(), -1) AS prev_month,  -- 2026-10-31
       EOMONTH(GETDATE(), 1)  AS next_month;  -- 2026-12-31

The comments show results for November 4, 2026. Leap years are handled: EOMONTH('2024-02-10') returns 2024-02-29.

First day of the month in SQL Server

All four return November 1, 2026, but the types differ:

DECLARE @d datetime = '2026-11-04T15:30:00';

-- date, SQL Server 2012+
SELECT DATEFROMPARTS(YEAR(@d), MONTH(@d), 1);
SELECT DATEADD(day, 1, EOMONTH(@d, -1));

-- same type as @d, SQL Server 2022+
SELECT DATETRUNC(month, @d);

-- datetime, any version
SELECT DATEADD(month, DATEDIFF(month, 0, @d), 0);
  • DATEFROMPARTS is the most readable, so it's my default.
  • DATEADD(day, 1, EOMONTH(@d, -1)) is the day after the end of the previous month. Handy when the query already uses EOMONTH.
  • DATETRUNC(month, @d) works in SQL Server 2022, Azure SQL Database, Managed Instance and Fabric. It keeps the input type: datetime stays datetime, date stays date, a string literal becomes datetime2(7). It doesn't need compatibility level 160: I ran it on a database at level 100.
  • DATEADD(month, DATEDIFF(month, 0, @d), 0) is the classic trick from before SQL Server 2012. 0 means 1900-01-01: count whole months since then and add them back.

First day of the previous and next month:

DECLARE @d datetime = '2026-11-04T15:30:00';

-- first day of the previous month: 2026-10-01
SELECT DATEADD(month, -1, DATEFROMPARTS(YEAR(@d), MONTH(@d), 1));

-- first day of the next month: 2026-12-01
SELECT DATEADD(day, 1, EOMONTH(@d));

More DATEADD examples are in SQL query for today's date minus year, month, day or minute.

Group by month

SELECT DATEFROMPARTS(YEAR(OrderDate), MONTH(OrderDate), 1) AS MonthStart,
       SUM(Amount) AS Total
FROM dbo.Orders
GROUP BY DATEFROMPARTS(YEAR(OrderDate), MONTH(OrderDate), 1)
ORDER BY MonthStart;

On SQL Server 2022 GROUP BY DATETRUNC(month, OrderDate) is shorter. GROUP BY EOMONTH(OrderDate) works too if you want each month labeled by its last day.

Pitfall: <= EOMONTH on a datetime column

EOMONTH returns a date. Compared with a datetime column, it turns into midnight of the last day. If OrderDate has a time part, OrderDate <= EOMONTH(@d) loses everything after 00:00 on that day. On my test table it dropped an order placed on October 31 at 18:45. Use a half-open range:

DECLARE @d date = '2026-10-15';

SELECT SUM(Amount) AS Total
FROM dbo.Orders
WHERE OrderDate >= DATEFROMPARTS(YEAR(@d), MONTH(@d), 1)
  AND OrderDate <  DATEADD(day, 1, EOMONTH(@d));

The range also works with an index on OrderDate. With this index the plan shows an Index Seek:

CREATE INDEX IX_Orders_OrderDate
    ON dbo.Orders (OrderDate) INCLUDE (Amount);

WHERE EOMONTH(OrderDate) = '2026-10-31' wraps the column in a function, and the same index is read in full (Index Scan). Without INCLUDE (Amount) the optimizer scanned my whole 500,000-row table even with the range: fetching Amount for each of the month's roughly 6,000 rows (Key Lookup) cost more.

SOMONTH: a helper function like EOMONTH

In 2016 I wrote a SOMONTH function for this. It had a datetime parameter, which failed on dates before 1753, and an extra datetime variable. Fixed version:

CREATE OR ALTER FUNCTION dbo.SOMONTH (@d date)
RETURNS date
AS
BEGIN
    RETURN DATEFROMPARTS(YEAR(@d), MONTH(@d), 1);
END;

CREATE OR ALTER needs SQL Server 2016 SP1 or later; on older versions use CREATE FUNCTION. Call it with the schema name: SELECT dbo.SOMONTH(GETDATE());

Scalar functions in GROUP BY are slow

Before SQL Server 2019 a scalar UDF runs once per row and forces a serial plan. SQL Server 2019 added scalar UDF inlining at compatibility level 150 and above: the optimizer pastes the function body into the query. But a UDF called in GROUP BY or ORDER BY is not inlined. I checked on SQL Server 2022: with GROUP BY dbo.SOMONTH(OrderDate) the plan still calls the function and can't go parallel.

Compute the month in CROSS APPLY and group by the resulting column. At compatibility level 150 and above this gets inlined:

SELECT m.MonthStart, SUM(o.Amount) AS Total
FROM dbo.Orders AS o
CROSS APPLY (SELECT dbo.SOMONTH(o.OrderDate) AS MonthStart) AS m
GROUP BY m.MonthStart;

On big tables, or on versions before 2019, skip the function and write DATEFROMPARTS inline as in the grouping example. Inlining doesn't fix WHERE dbo.SOMONTH(OrderDate) = ... either: the column is still wrapped in a function, so filter by a date range.

To check whether SQL Server can inline your function:

SELECT is_inlineable
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('dbo.SOMONTH');  -- 1
I do product consulting, build MVPs and teach people to build products themselves. Work with me →
Related posts: