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
Please help me
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.