SQL: How to produce next date given month and day

Question

In my table I have a Month(tinyint) and a Day(tinyint) field. I would like to have a function that takes this month and day and produces a datetime for the next date(including year) given this month and day.

So if I had Month = 9, Day = 7 it would produce 9/7/2009.
If I had Month 1, Day 1 it would produce 1/1/2010.

Answer 1

something like this would work. It's variation on your method, but it doesn't use the MM/DD/YYYY literal format, and it won't blowup against bad input (for better or for worse).

declare @month tinyint
declare @day tinyint
set @month = 9
set @day = 1

declare @date datetime

-- this could be inlined if desired
set @date = convert(char(4),year(getdate()))+'0101'
set @date = dateadd(month,@month-1,@date)
set @date = dateadd(day,@day-1,@date)

if @date <= getdate()-1
  set @date = dateadd(year,1,@date)

select @date

Alternatively, to create a string in YYYYMMDD format:

set @date = 
  right('0000'+convert(char(4),year(getdate())),4)
+ right('00'+convert(char(2),@month),2)
+ right('00'+convert(char(2),@day),2)

Another method, which avoids literals all together:

declare @month tinyint
declare @day tinyint
set @month = 6
set @day = 24

declare @date datetime
declare @today datetime

-- get todays date, stripping out the hours and minutes
-- and save the value for later
set @date = floor(convert(float,getdate()))
set @today = @date

-- add the appropriate number of months and days
set @date = dateadd(month,@month-month(@date),@date)
set @date = dateadd(day,@day-day(@date),@date)

-- increment year by 1 if necessary
if @date < @today set @date = dateadd(year,1,@date)

select @date