How to get last day of the given month & How to get the Month number of Month Name?

Trick - Get the first day of the month, then add 1 month to it, then subtract 1 day

[ Just Copy & Paste ]

declare @Month int,@Year int

declare @LastDayofMonth varchar(25)

set @Month = 2
set @Year = 2012

set @LastDayofMonth = dateadd(dd,-1,Dateadd(mm,1,cast(@Month as varchar(2))+'/1/'+cast(@Year as varchar(4))))

select CONVERT(datetime,@LastDayofMonth,106)

---------------------------------------

declare @MonthName varchar(25)

set @MonthName = 'September'
set @MonthName = 'Sep'

select month(CAST(@MonthName + '1 2001' as datetime))