SQL EOMONTH Function

EOMONTH returns the last day of the month for the specified date.

Optionally, a month offset can be added to offset the specified date.

Example

#

This example returns the last day of the month for a specified date.

SELECT EOMONTH('2022-09-25') AS 'End of Month'
Result:  1 record
End of Month
2022-09-30

Using EOMONTH

A common use is to get the last day of today's month.

SELECT EOMONTH(GETDATE()) as 'End of Month for Today'
Result:  1 record
End of Month for Today
2021-12-31

Syntax

Syntax of the EOMONTH function .

EOMONTH(input_date, months_to_add) 

input_date -- the date for which end of month will be calculated.

months_to_add -- optional. a month offset that is added to the input_date.


More Examples

EOMONTH with OFFSET.

ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount
CUSTOMER
Id
FirstName
LastName
City
Country
Phone
Problem: List the last product return dates at the end of month, one month after the order date.
SELECT FirstName, LastName, OrderNumber, OrderDate, 
       EOMONTH(OrderDate, 1) as 'May Return Until'
  FROM [Order] O
  JOIN Customer C ON C.Id = O.CustomerId
Result:  830 records
FirstName LastName OrderNumber OrderDate May Return Until
Paul Henriot 542378 2012-07-04 00:00:00.000 2012-08-31
Karin Josephs 542379 2012-07-05 00:00:00.000 2012-08-31
Mario Pontes 542380 2012-07-08 00:00:00.000 2012-08-31
Mary Saveley 542381 2012-07-08 00:00:00.000 2012-08-31
Pascale Cartrain 542382 2012-07-09 00:00:00.000 2012-08-31

EOMONTH with negative OFFSET.

Problem: Get the last day of the month 6 months before Sep 2022.
SELECT EOMONTH('2022-09-25', -6) AS 'EOMonth 6 months prior'
Result:  1 record
EOMonth 6 months prior
2021-03-31

You may also like



Guides


vsn 3.1