sqlsql-server

SQL Server While loop for last day of month


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


Solution

  • After all the things everybody said, here comes the update:

    Declare @FromDate   DateTime
    ;
    
    Select  @FromDate   =   Convert(DateTime, '2012-1-1')
    ;
    
    With    CTE (
        Level
    ,   FirstDayOfMonth
    ,   Value
    )   As  (
        Select  1       As  Level
        ,   @FromDate
        ,   DateAdd(Day, -1, DateAdd(Month, 1, @FromDate))
    Union   All
        Select  Level
        +   1
        ,   FirstDayOfMonth
    ,   DateAdd(Day, -1, DateAdd(Month, Level + 1, FirstDayOfMonth))
            From    CTE
            Where   Level   <   12
    )   Select  Value
            From    CTE
            Order   By  Value
    

    It's a CTE (Common Table Expression). The first value should be the first day of the second month, after adding -1 day it will be the last day of January. And of course it will give you the result.

    What I did was to just use DateAdd(Day, -1, [The first day of the next month]), that simple!

    Just for fun, this way, I did start with the first day of the February, and let the engine detects the last day of January! :D Maybe next year after another sandy's cousin storm earth changes its way and January wasn't 31 days anymore! So your code will work then...

    Thank you everyone for making me update this answer.

    Hope it helps.