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:
New Built in Functions introduced in Sql Server | |
CONVERSION FUNCTIONS | |
PARSE | TRY_PARSE |
TRY_CONVERT | |
STRING FUNCTIONS | |
CONCAT | FORMAT |
LOGICAL FUNCTIONS | |
CHOOSE | IIF |
DATE AND TIME FUNCTIONS | |
EOMONTH | |
DATEFROMPARTS | DATETIMEFROMPARTS |
SMALLDATETIMEFROMPARTS | DATETIME2FROMPARTS |
TIMEFROMPARTS | DATETIMEOFFSETFROMPARTS |
I love your BLOG. Thanks for educating the community. I learned something today. Have you ever made any Youtube videos?
Thanks abunch
Thanks Kris for your comments. As of today, I have not created any Youtube videos.
Superb sir,
I love sql server. And by reading your blog even i am interested more in it.
Thank you