{"id":46008,"date":"2025-11-07T06:58:47","date_gmt":"2025-11-06T20:58:47","guid":{"rendered":"https:\/\/database.guide\/?p=46008"},"modified":"2025-11-07T06:58:48","modified_gmt":"2025-11-06T20:58:48","slug":"understanding-the-eomonth-function-in-sql-server","status":"publish","type":"post","link":"https:\/\/database.guide\/understanding-the-eomonth-function-in-sql-server\/","title":{"rendered":"Understanding the EOMONTH() Function in SQL Server"},"content":{"rendered":"\n

SQL Server has an EOMONTH()<\/code> function that returns the last day of the month for a given date. It can be quite handy when you’re working with date calculations in your queries, as it saves you from having to perform calculations just to get the end of the month.<\/p>\n\n\n\n\n\n\n\n

What EOMONTH()<\/code> Does<\/h2>\n\n\n\n

The EOMONTH()<\/code> simply takes a date and returns the last day of that month. If you pass it January 15th, 2026, it’ll give you back January 31st, 2026. Pass it February 3rd, 2024, and you get February 29th, 2024 (since 2024 is a leap year). The function automatically handles all the quirks of the calendar. For example, it automatically calculates leap years, months with 30 days versus 31 days, February’s special behavior, etc.<\/p>\n\n\n\n

Before this function was introduced in SQL Server 2012, we had to write complex expressions involving DATEADD()<\/a><\/code> and DATEDIFF()<\/a><\/code> to achieve the same result. Now you can do it with a single, readable function call.<\/p>\n\n\n\n

Basic Syntax<\/h2>\n\n\n\n

The syntax for EOMONTH()<\/code> goes like this:<\/p>\n\n\n\n

EOMONTH(start_date [, month_to_add])<\/code><\/pre>\n\n\n\n
The first argument, start_date<\/code><\/em>, is required and represents the date you want to work with. The second argument, month_to_add<\/code><\/em>, is optional and lets you offset the calculation by a certain number of months. This offset can be positive (to go forward in time) or negative (to go backward).<\/p>\n\n\n\n
Simple Example<\/h2>\n\n\n\n
Let’s start with the most basic usage. If you want to find the last day of the current month:<\/p>\n\n\n\n
SELECT EOMONTH(GETDATE()) AS LastDayOfCurrentMonth;<\/code><\/pre>\n\n\n\n
This returns the last day of whatever month you’re currently in. If today is March 15th, 2026, this query returns March 31st, 2026.<\/p>\n\n\n\n
You can also use it with a specific date:<\/p>\n\n\n\n
SELECT EOMONTH('2026-06-15') AS LastDayOfJune;<\/code><\/pre>\n\n\n\n
The function automatically figures out that June has 30 days and returns the appropriate date.<\/p>\n\n\n\n
Using the Month Offset Parameter<\/h2>\n\n\n\n
The (optional) second argument allows us to calculate the end of months in the past or future relative to your starting date.<\/p>\n\n\n\n
To get the last day of next month:<\/p>\n\n\n\n
SELECT EOMONTH(GETDATE(), 1) AS LastDayOfNextMonth;<\/code><\/pre>\n\n\n\n
If you run this in March 2026, it returns April 30th, 2026. The function first determines what “next month” means relative to the current date, then finds the last day of that month.<\/p>\n\n\n\n
Going backward works just as well:<\/p>\n\n\n\n
SELECT EOMONTH(GETDATE(), -1) AS LastDayOfPreviousMonth;<\/code><\/pre>\n\n\n\n
You can use larger offsets too. Here’s how to get the last day of the month six months from now:<\/p>\n\n\n\n
SELECT EOMONTH(GETDATE(), 6) AS LastDaySixMonthsOut;<\/code><\/pre>\n\n\n\n
Calculating Monthly Totals<\/h2>\n\n\n\n
One common use case for EOMONTH()<\/code> is in financial reporting, where you often need to group or filter data by month-end dates.<\/p>\n\n\n\n
Let’s say you have a sales table and want to calculate monthly totals with the month-end date:<\/p>\n\n\n\n
SELECT \n    EOMONTH(OrderDate) AS MonthEnd,\n    SUM(OrderAmount) AS TotalSales\nFROM Orders\nWHERE OrderDate >= '2024-01-01'\nGROUP BY EOMONTH(OrderDate)\nORDER BY MonthEnd;<\/code><\/pre>\n\n\n\n
This query groups all orders by their month and shows each month’s total alongside its end date, making it great for monthly reports.<\/p>\n\n\n\n
Another scenario involves finding the first day of a month. While EOMONTH()<\/code> gives you the last day, you can use it to calculate the first day too:<\/p>\n\n\n\n
SELECT DATEADD(DAY, 1, EOMONTH(GETDATE(), -1)) AS FirstDayOfCurrentMonth;<\/code><\/pre>\n\n\n\n
This works by getting the last day of the previous month, then adding one day to it.<\/p>\n\n\n\n
Working with Subscription and Billing Cycles<\/h2>\n\n\n\n
EOMONTH()<\/code> can be useful when dealing with subscriptions or billing periods. Imagine you have a subscription system where users are billed on the last day of each month. You can easily calculate upcoming billing dates:<\/p>\n\n\n\n
SELECT \n    UserID,\n    SubscriptionStartDate,\n    EOMONTH(SubscriptionStartDate) AS FirstBillingDate,\n    EOMONTH(SubscriptionStartDate, 1) AS SecondBillingDate,\n    EOMONTH(SubscriptionStartDate, 2) AS ThirdBillingDate\nFROM Subscriptions;<\/code><\/pre>\n\n\n\n
This gives you a clear view of when each user will be billed over the next few months.<\/p>\n\n\n\n
Handling Date Ranges<\/h2>\n\n\n\n
When you need to query data for a complete month, EOMONTH()<\/code> makes it easy to define your date range precisely. Here’s how you might pull all orders from the previous complete month:<\/p>\n\n\n\n
SELECT *\nFROM Orders\nWHERE OrderDate >= DATEADD(DAY, 1, EOMONTH(GETDATE(), -2))\n  AND OrderDate <= EOMONTH(GETDATE(), -1);<\/code><\/pre>\n\n\n\n
This query finds the last day of the previous month (using -1), then goes back to the day after the last day of the month before that (using -2), giving you exactly one complete month of data.<\/p>\n\n\n\n
Data Type Considerations<\/h2>\n\n\n\n
EOMONTH()<\/code> accepts various date-related data types as input, including date<\/code>, datetime<\/code>, datetime2<\/code>, datetimeoffset<\/code>, smalldatetime<\/code>, and even varchar representations of dates. The function always returns a date<\/code> type though. This is important to remember because the returned value doesn’t include a time component.<\/p>\n\n\n\n
If you pass in a datetime value like ‘2024-03-15 14:30:00<\/code>‘, EOMONTH()<\/code> returns ‘2024-03-31<\/code>‘ with no time portion. If you need to maintain the time component or set it to a specific value, you’ll need to convert or cast the result:<\/p>\n\n\n\n
SELECT CAST(EOMONTH(GETDATE()) AS DATETIME) + CAST('23:59:59' AS DATETIME) AS EndOfMonthWithTime;<\/code><\/pre>\n\n\n\n
That hardcodes the time component. You could also generate the time component like this:<\/p>\n\n\n\n
SELECT DATEADD(MILLISECOND, -3, DATEADD(DAY, 1, CAST(EOMONTH(GETDATE()) AS DATETIME))) AS EndOfMonthEndOfDay;<\/code><\/pre>\n\n\n\n
Or using datetime2<\/code>:<\/p>\n\n\n\n
SELECT DATEADD(MILLISECOND, -3, DATEADD(DAY, 1, CAST(EOMONTH(GETDATE()) AS DATETIME2))) AS EndOfMonthEndOfDay;<\/code><\/pre>\n\n\n\n
You could do the following to keep the current time component:<\/p>\n\n\n\n
SELECT DATEADD(DAY, DATEDIFF(DAY, CAST(GETDATE() AS DATE), EOMONTH(GETDATE())), GETDATE()) AS EndOfMonthWithOriginalTime;<\/code><\/pre>\n\n\n\n
Or this:<\/p>\n\n\n\n
SELECT CAST(CAST(EOMONTH(GETDATE()) AS VARCHAR(10)) + ' ' + CAST(CAST(GETDATE() AS TIME) AS VARCHAR(12)) AS DATETIME) AS EndOfMonthWithCurrentTime;<\/code><\/pre>\n\n\n\n
Error Handling and Edge Cases<\/h2>\n\n\n\n
EOMONTH()<\/code> is pretty robust, but there are a few things to watch out for. If you pass a NULL value as the start date, the function returns NULL<\/code>: <\/p>\n\n\n\n
SELECT EOMONTH(NULL) AS Result;<\/code><\/pre>\n\n\n\n
This is standard SQL Server behavior, but it’s worth keeping in mind when you’re working with columns that might contain NULL values.<\/p>\n\n\n\n
The month offset parameter also has limits. While you can use large positive or negative numbers, the resulting date must fall within SQL Server’s supported date range. If you try to calculate a date that would overflow this range, you’ll get an error.<\/p>\n\n\n\n
Combining EOMONTH()<\/code> for Other Uses<\/h2>\n\n\n\n
EOMONTH()<\/code> plays well with other SQL Server date functions, letting you build more complex date calculations. For instance, you can combine it with DATEDIFF()<\/a><\/code> to find out how many days remain in the current month:<\/p>\n\n\n\n
SELECT DATEDIFF(DAY, GETDATE(), EOMONTH(GETDATE())) AS DaysLeftInMonth;<\/code><\/pre>\n\n\n\n
Or use it with various date part functions to create more sophisticated groupings:<\/p>\n\n\n\n
SELECT \n    YEAR(OrderDate) AS OrderYear,\n    MONTH(OrderDate) AS OrderMonth,\n    EOMONTH(OrderDate) AS MonthEnd,\n    COUNT(*) AS OrderCount\nFROM Orders\nGROUP BY YEAR(OrderDate), MONTH(OrderDate), EOMONTH(OrderDate);<\/code><\/pre>\n\n\n\n
Performance Considerations<\/h2>\n\n\n\n
EOMONTH()<\/code> is a deterministic function, meaning it always returns the same result for the same input. This makes it eligible for use in computed columns<\/a> and indexes<\/a>. If you frequently filter or group by month-end dates, you could create a computed column using EOMONTH()<\/code> and even index it for better performance:<\/p>\n\n\n\n
ALTER TABLE Orders\nADD MonthEnd AS EOMONTH(OrderDate) PERSISTED;\n\nCREATE INDEX IX_Orders_MonthEnd ON Orders(MonthEnd);<\/code><\/pre>\n\n\n\n
The PERSISTED<\/code> keyword tells SQL Server to physically store the computed value, which can speed up queries that reference this column.<\/p>\n\n\n\n
Comparing with Alternative Approaches<\/h2>\n\n\n\n
Before EOMONTH()<\/code> was introduced, workarounds would be needed to achieve the same result. A common pattern looked something like this:<\/p>\n\n\n\n
SELECT DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)) AS LastDayOfMonth;<\/code><\/pre>\n\n\n\n
This works, but it’s harder to read and more prone to errors. EOMONTH()<\/code> makes your code cleaner and more maintainable. If you’re maintaining older SQL Server code (pre-2012), you might encounter these patterns, but for any modern SQL Server instance, EOMONTH()<\/code> is the way to go.<\/p>\n\n\n\n
Dashboard Example with CTE<\/h2>\n\n\n\n
Suppose you’re building a dashboard that shows monthly sales trends and needs to highlight which months met their sales targets. The following example uses EOMONTH()<\/code> with a common table expression (CTE)<\/a> to achieve this:<\/p>\n\n\n\n
WITH MonthlySales AS (\n    SELECT \n        EOMONTH(OrderDate) AS MonthEnd,\n        SUM(OrderAmount) AS TotalSales\n    FROM Orders\n    WHERE OrderDate >= DATEADD(MONTH, -12, GETDATE())\n    GROUP BY EOMONTH(OrderDate)\n)\nSELECT \n    MonthEnd,\n    TotalSales,\n    CASE \n        WHEN TotalSales >= 15000 THEN 'Target Met'\n        ELSE 'Below Target'\n    END AS TargetStatus\nFROM MonthlySales\nORDER BY MonthEnd DESC;<\/code><\/pre>\n\n\n\n
This query uses EOMONTH()<\/code> to group sales by month, looks at the last 12 months of data, and evaluates performance against a target.<\/p>\n\n\n\n
Wrapping Up<\/h2>\n\n\n\n
SQL Server’s EOMONTH()<\/code> function proves its worth in daily database work by handling month-end calculations cleanly and reliably. It removes the guesswork from calendar arithmetic and keeps your queries readable when you revisit them months later. <\/p>\n\n\n\n
The month offset parameter extends its usefulness beyond simple lookups, letting you build queries that calculate billing schedules, compare month-over-month trends, or filter historical data without wrestling with complex date math. <\/p>\n\n\n\n
For anyone working with time-series data, financial records, or periodic reporting in SQL Server 2012 or later, EOMONTH()<\/code> handles a common problem efficiently enough that you’ll find yourself reaching for it regularly.<\/p>\n","protected":false},"excerpt":{"rendered":"
SQL Server has an EOMONTH() function that returns the last day of the month for a given date. It can be quite handy when you’re working with date calculations in your queries, as it saves you from having to perform calculations just to get the end of the month.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[68,115,77,61,20],"class_list":["post-46008","post","type-post","status-publish","format-standard","hentry","category-sql-server","tag-date-functions","tag-dates","tag-mssql","tag-t-sql","tag-what-is"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/46008","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=46008"}],"version-history":[{"count":5,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/46008\/revisions"}],"predecessor-version":[{"id":46038,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/46008\/revisions\/46038"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=46008"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=46008"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=46008"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}