Introduction
EOMONTH is new built-in Date and Time function which is introduced with SQL Server 2012. It Returns the last day of the month that contains the given date, with an optional offset.
Example: If given date is 24-SEP-2013, output(EOMONTH ) will be 30-SEP-2013( for September month last day is 30th). It considers Leap year as well(input: 2016-02-15 and Output: 2016-02-29).
Syntax:
EOMONTH ( start_date [, month_to_add ] )
Arguments:
start_date - Date value for which to return the last day of the month
month_to_add (optional offset ) - Integer parameter to add/subtract specified month to start_dateReturn Type: Date
In order understand EOMONTH function easily we will see some examples.
Example 1: EOMONTH with DATETIME Type
SELECT GETDATE() AS CurrentDateTime
DECLARE @date1 DATETIME = GETDATE();
SELECT EOMONTH(@date1) AS EOFCurrentMonth;
Output:
CurrentDateTime
2013-09-24 12:59:33.650
EOFCurrentMonth
2013-09-30
Example2: EOMONTH with offset ( Next/Prev month’s lastdate)
DECLARE @date2 DATETIME ='2013-09-24'
SELECT EOMONTH ( @date2, 1) AS EOFNextMonth
SELECT EOMONTH ( @date2, -1) AS EOFPrevMonth
Output:
EOFNextMonth
2013-10-31
EOFPrevMonth
2013-08-31
Example3: EOMONTH with VARCHAR Type and implicit conversion
DECLARE @date3 AS VARCHAR(10)= '2013-09-24'
SELECT EOMONTH(@date3) AS EOFCurrentMonth
Output:
EOFCurrentMonth
2013-09-30
Example4: EOMONTH Invalid Date Type
DECLARE @invalid_date AS DATETIME= '2013-24-09'
SELECT EOMONTH(@invalid_date) AS EOFCurrentMonth
Output:
Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
EOFCurrentMonth: NULL (1 row(s) affected)
EOMONTH equivalent in SQL Server 2008 R2 and below:
EOMONTH function available with SQL Server 2012 so in order get EOMONTH behavior with below 2012 versions you can use below SQL.
DECLARE @date5 AS DATETIME = '2013-09-24'
SELECT DATEADD(MONTH,DATEDIFF(MONTH,-1, @date5),-1) AS EOFMonthWithSQLLessThan2012
Output:
EOFMonthWithSQLLessThan2012
2013-09-30 00:00:00.000