Home » SQL EOMONTH()

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]

As you can see, the output of first statement is the last day of previous month that is JUNE as month of current given date is JULY so the output is ‘1996-06-30’.
In similar way the output of second statement is the last day of third previous month that is APRIL as month of current given date is JULY so the third previous month would by APRIL and the last day of april would be  ‘1996-04-30’.
If you only want a day part from date then you can use DAY() function to get the last day only from date.
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]
Recommended for you




Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.