April 19, 2012

Get last day of a given month in SQL Server

One way to find the last day of a given month is to get the next month’s first day and then get the previous day of the particular day. The complete SQL query has given below. The query is not much complex but you may need to do some date calculation here. The query can be written as:

declare @mm varchar(2), @yy varchar(4), @dd varchar(2), @date datetime
set @date=convert(datetime, '4/16/2012')
Set @mm=(Select datepart(M, @date))
Set @yy=(Select datepart(YYYY, @date))
Set @dd= (Select Day(DATEADD(month, 1, DateAdd(day, 1-day(@date), @date))-1))
Select DATENAME(DW, ''+@mm+'/'+@dd+'/'+@yy+'')


For: 4/16/2012 :- Monday
For: 5/16/2012:- Thursday