EOMONTH():
SELECT EOMONTH(DATEFROMPARTS(2025, 4, 1)) AS EndOfMonthDate Output: 2025-04-30- SQL Server 2012+ offers
EOMONTH()for retrieving the end of any given month. - Legacy versions (SQL Server 2005/2008) lack this built-in function.
Let’s create a user-defined scalar function dbo.fn_EndOfMonth to compute the last day of any month using date manipulation techniques.
Methodology:
- Builds the first day of the next month from the input year and month.
- Subtracts one day to get the last day of the target month.
CREATE FUNCTION dbo.fn_EndOfMonth (@Year INT, @Month INT)
RETURNS DATE
AS
BEGIN
DECLARE @FirstOfNextMonth DATE
DECLARE @EndOfMonth DATE
-- Step 1: Get the 1st day of the next month
SET @FirstOfNextMonth = DATEADD(MONTH, 1, CAST(CONCAT(@Year, RIGHT('0' + CAST(@Month AS VARCHAR(2)), 2), '01') AS DATE))
-- Step 2: Subtract 1 day to get last day of target month
SET @EndOfMonth = DATEADD(DAY, -1, @FirstOfNextMonth)
RETURN @EndOfMonth
END
Example
SELECT dbo.fn_EndOfMonth(2025, 4) AS EndOfMonthOutput: 2025-04-30
