Howdy Everyone.
Most of the times I see, people getting up with new ideas to get the last day of the month…
But the simplest method we can use in SQL Server is by adding 1 to month and subtracting the number of days in the date, confusing….
Well let me explain with an example
Let us take the date 20-AUG-2011. Now if we add 1 to the month by using DATEADD function.. the result will be 20-SEP-2011. Now the day part of the resultant date is 20 and if we subtract the 20 from the resultant by using dateadd the result will be 30-AUG-2011.
The code is given below
DECLARE @input_date datetime
set @input_date = getdate() -- for testing purposes
select dateadd(dd,datepart(dd,@input_date)*-1,dateadd(mm,1,@input_date))