SQL Server While 循环获取每月最后一天

Question

我想要每个月的最后一天，所以我写了这样的查询

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

但是我得到的结果是这样的

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

请帮助我

Answer 1

你可能会更好:-

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