EOMONTH is one of the new built-in Date and Time function introduced as a Part of Sql Server 2012. It returns the last date of the Month that contains the specified date in it. It is a Sql Server native function, not dependent on the .NET CLR.

SYNTAX: EOMONTH ( start_date [, month_to_add ] )

Parameters:
start_date: Date Expression representing the date, for which the last date of the Month is returned
month_to_add: This is an optional integer parameter, if it is specified then specified number of months is added to the start_date and then last date of the month for the resultant date is returned.

Return Type: Date

Example 1: EOMONTH – start_date as DATETIME type

DECLARE @date AS DATETIME = '2012/06/22'
SELECT EOMONTH(@date) EOFMONTH

Result:
EOFMONTH
———-
2012-06-30

Example 2: Last date of the month in previous versions of Sql Server like Sql Server 2008, 2005, 2000.

DECLARE @date AS DATETIME = '2012/06/22'
SELECT DATEADD(MONTH,datediff(MONTH,-1, @date),-1) EOFMONTH

Result:
EOFMONTH
———————–
2012-06-30 00:00:00.000

Example 3: EOMONTH – Start_date as valid date expression of type VARCHAR

DECLARE @date AS varchar(10)= '2012/06/22'
SELECT EOMONTH(@date) EOFMONTH

Result:
EOFMONTH
———-
2012-06-30

Example 4: Current/Previous/Previou-to-Previous month’s lastdate using EOMONTH

DECLARE @date AS DATETIME = '2012/06/22'
SELECT EOMONTH(@date) 'Current Month',
                EOMONTH(@date, -1) 'Previous Month',
                EOMONTH(@date, -2) 'Previous-to-Previous Month'

Result:
Current Month Previous Month Previous-to-Previous Month
————- ————– ————————–
2012-06-30 2012-05-31 2012-04-30

Example 5: Current/Next/Next-To-Next month’s last date using EOMONTH

DECLARE @date AS DATETIME = '2012/06/22'
SELECT EOMONTH(@date) 'Current Month',
                EOMONTH(@date, 1) 'Next Month',
                EOMONTH(@date, 2) 'Next-to-Next Month'

Result:
Current Month Next Month Next-to-Next Month
————- ———- ——————
2012-06-30 2012-07-31 2012-08-31

Example 6: Start Date of the Month using EOMONTH

DECLARE @date AS DATETIME = '2012/06/22'
SELECT DATEADD(DAY, 1, EOMONTH(@date, -1)) 
                AS 'Current Month Start Date'

Result:
Current Month Start Date
————————
2012-06-01

Example 7: Invalid start_date

SELECT EOMONTH('2012/02/31') EOFMONTH

Result:
EOFMONTH
———-
Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.

You may like to read the below new built-in function’s introduced in Sql Server 2012: