Unlike finding the first day of the month, finding the last day of the month is a straightforward deal in SQL Server. There is a specific inbuilt date function called EOMONTH to find the end of month. This function was introduced in SQL Server 2012.
Using the EOMONTH function, you can find the end of month of a given date or current date or even you have the option to increase or decrease the month using the second parameter. Let’s see how it works:
Examples to Get Last Day Of Month
/* For a given date */
DECLARE @DateValue DATETIME = '2019-05-13';
SELECT EOMONTH( @DateValue ) AS 'End Of Month';
GO
/* Result */
2019-05-31
/* Next month */
DECLARE @DateValue DATETIME = '2019-05-13';
SELECT EOMONTH( @DateValue, 1 ) AS 'End Of Next Month';
GO
/* Result */
2019-06-30
/* Previous month */
DECLARE @DateValue DATETIME = '2019-05-13';
SELECT EOMONTH( @DateValue, -1 ) AS 'End Of Previous Month';
GO
/* Result */
2019-04-30
/* After 6 months */
DECLARE @DateValue DATETIME = '2019-05-13';
SELECT EOMONTH( @DateValue, 6 ) AS 'End Of Next Month';
GO
/* Result */
2019-11-30
Reference
- About EOMONTH at Microsoft Docs.