SQL Server While loop for last day of month

Question

I want last day of every month so I wrote query like

declare @date datetime set @date='31-jan-2012' 
while @date<='31-Dec-2012' 
  Begin 
    print @date 
    set @date= DATEADD(M,1,@date)
  End

But I got Result like

Jan 31 2012 12:00AM
Feb 29 2012 12:00AM
Mar 29 2012 12:00AM
Apr 29 2012 12:00AM
May 29 2012 12:00AM
Jun 29 2012 12:00AM
Jul 29 2012 12:00AM
Aug 29 2012 12:00AM
Sep 29 2012 12:00AM
Oct 29 2012 12:00AM
Nov 29 2012 12:00AM
Dec 29 2012 12:00AM

Please help me

Answer 1

You might be better off with:-

declare @date datetime;
declare @lastDay datetime;

-- Start on the first day of the next month
-- This'll ensure that your month add doesn't throw 
-- weird results
set @date='01-Feb-2012'
while @date<='01-Jan-2013'

Begin
    -- Get last day of previous month
    set @lastDay = DATEADD(DAY, -1, @date)

    -- print that
    print @lastDay

    -- increment @date to move one month into the future.
    set @date= DATEADD(month,1,@date)
End