EOMonth function in sql Server 2012

EOMonth function in sql Server 2012.......................

EOMonth:  its returns the last day of the month of specified date

Syntax-: EOMonth( start_date , month_to_add );

Parameters:

1      start_date -: the date for  which to return the last day of month

        month_to_add-: optional number of months to add to start_date, EOMonth adds specified   number of months to be start_date ,and then  returns  the last day of the month for the resulting date

Example: return last day of the March

Select  EOMonth('03/05/2017')  as LastDate

--Sql Query -----------------

create database tiwari

use tiwari

create table EmpDetails

(

EmpId int primary key,

Name varchar(50) unique,

DOB Date

)

insert into EmpDetails values(2345,'Kush Tiwari','08/11/1987')

insert into EmpDetails values(1344,'Ravi Pratap Singh','08/02/1975')

insert into EmpDetails values(2342,'Manoj Yadav','03/11/1990')

--Example 1: Invalid start_date

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

--Result

Msg 241, Level 16, State 1, Line 1

Conversion failed when converting date and/or time from character string.

--Example 2:

select * from EmpDetails

select empid,name as FullName,'Date Of Birth'=dob,EOMonth(DOB) as LastDay from EmpDetails

--Example 3:

select empid,name,dob,DatePart(dd,EOMonth(DOB)) as LastDay from EmpDetails

--Result 31

--Example 4:

DECLARE @date AS DATETIME = '2015/01/22'

SELECT EOMONTH(@date) EOFMONTH

-- Result

--2015-01-31

--Example 5:

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

DECLARE @date AS DATETIME='2012/02/22'

SELECT DATEADD(MONTH,datediff(MONTH,-1, @date),-1) EOFMONTH

-- Result

--2012-02-29 00:00:00.000

--Example 6:

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

DECLARE @date AS DATETIME ='2015/01/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

                     2015-05-31      2014-12-31         2014-11-30

--Example 7: Start Date of the Month using EOMONTH

DECLARE @date AS DATETIME ='2015/05/22'

SELECT DATEADD(DAY, 1, EOMONTH(@date, -1)) AS 'Current Month Start Date'

--Result 2015-05-01

--Example 8 – Start_date as valid date expression of type VARCHAR

DECLARE @date AS varchar(10)='2015/01/22'

SELECT convert(varchar(50),EOMONTH(@date),106) ResultDate

--Result 31-Jan-2015