SQL EOMONTH()

SQL EOMONTH() function is a date function, it returns the last day of the month of a specified date, with an optional offset.

SYNTAX

EOMONTH (Date, [,offset])

Date is a date that is used to get a last day of month.

Second argument offset is an integer that specifies the number of months to add to the date .

Positive offset value returns next month’s last day and Negative offset value returns previous month’s last day.

Lets look at an example of function EOMONTH() in SQL Server.

Using EOMONTH Function to get last day of the month of specific date

Following statement, uses EOMONTH function and returns the last day of the month for given date.

DECLARE @orderdate AS DATETIME
SET @orderdate ='1996-07-15 00:00:00.000' 
SELECT EOMONTH(@orderdate) AS [Last Day Of Month]

As you can see, It returns a last day of the month for date =‘1996-07-15 00:00:00.000’. Here we do not provide an offset as we want the last day of given date only.

Using EOMONTH function to get last day of next month from given date

Following statement uses EOMONTH function, and returns the last day of next month from given date.

Here we have provided an offset value 1, that means EOMONTH month adds 1 month to the input date ‘1996-07-15 00:00:00.000’ and returns next month last day.

DECLARE @orderdate AS DATETIME
SET @orderdate ='1996-07-15 00:00:00.000' 
SELECT EOMONTH(@orderdate, 1) AS [Last Day Of Next Month]

SET @orderdate ='1996-07-15 00:00:00.000' 
SELECT EOMONTH(@orderdate,-1) AS [Last Day Of Previous Month 1] 
select EOMONTH(@orderdate,-3) as [Last Day Of Previous Month 2]

DECLARE @orderdate AS DATETIME
SET @orderdate ='1996-07-15 00:00:00.000' 
SELECT DAY(EOMONTH(@orderdate,-1)) AS [Last Day Of Previous Month 1] 
select DAY(EOMONTH(@orderdate,-3)) as [Last Day Of Previous Month 2]