OK, final one...!
You only pass day to this + it will handle all possible date permutations. e.g if you pass 31 to this as date, it will just get the 'last date available' in months that don't have 31 days
CREATE PROCEDURE [dbo].[GetAllMonthsForDay]
(
@Day INT
)
AS
SET NOCOUNT ON
DECLARE @CurrentDate DATETIME
DECLARE @FirstDate DATETIME
DECLARE @LastDate DATETIME
DECLARE @Today DATETIMe
DECLARE @Months TABLE (DateField DATETIME)
DECLARE @Year INT
IF (@Day < 1 OR @Day > 31)
BEGIN
RAISERROR('Invalid day specified for date function', 16, 1)
RETURN
END
SET @Today = GETDATE()
SET @Year = DATEPART(yyyy, @Today )
SET @FirstDate = CAST(CAST(1 AS varchar) + '-' + CAST(@Day AS varchar) + '-' + CAST(@Year AS varchar) AS DATETIME)
SET @LastDate = CAST(CAST(12 AS varchar) + '-' + CAST(@Day AS varchar) + '-' + CAST(@Year AS varchar) AS DATETIME)
SET @CurrentDate = @FirstDate
While @CurrentDate <= @LastDate
Begin
Insert Into @Months Values(@CurrentDate)
IF (DATEPART(dd, @CurrentDate) != @Day)
BEGIN
Set @CurrentDate = DateAdd(m,1,@CurrentDate)
SET @CurrentDate = CAST(CAST(DATEPART(mm, @CurrentDate) AS varchar) + '-' + CAST(@Day AS varchar) + '-' + CAST(@Year AS varchar) AS DATETIME)
END
ELSE
BEGIN
Set @CurrentDate = DateAdd(m,1,@CurrentDate)
END
End
Select * From @Months
RETURN @@ERROR
SET NOCOUNT OFF