SQL: how to create the next date specified month and day

Question

In my table, I have month (tinyint) and day (tinyint). I would like to have a function that takes this month and day, and gives the date and time on the next date (including the year), given this month and day.

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

Answer 1

something like this will work. This is a variation of your method, but it does not use the alphabetic format MM / DD / YYYY, and it will not explode from bad input (better or 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 the format YYYYMMDD:

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

, :

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

Answer 2

sql. , ...

DECLARE @month tinyint,
    @day tinyint,
    @date datetime

SET @month = 1
SET @day = 1

-- SET DATE TO DATE WITH CURRENT YEAR
SET @date = CONVERT(datetime, CONVERT(varchar,@month) + '/' + CONVERT(varchar,@day) + '/' + CONVERT(varchar,YEAR(GETDATE())))


-- IF DATE IS BEFORE TODAY, ADD ANOTHER YEAR
IF (DATEDIFF(DAY, GETDATE(), @date) < 0)
BEGIN
    SET @date = DATEADD(YEAR, 1, @date)
END

SELECT @date

Answer 3

Here is a solution with PostgreSQL

your_date_calculated = Year * 10000 + Month * 100 + Day 

gives a date, e.g. 20090623.

select cast( cast( your_date_calculated as varchar )  as date ) + 1 

Answer 4

Here is my version. The core of this is just two lines, using a function DATEADD, and it does not require any conversion to / from lines, floats, or anything else:

DECLARE @Month TINYINT
DECLARE @Day TINYINT

SET @Month = 9
SET @Day = 7

DECLARE @Result DATETIME

SET @Result =
    DATEADD(month, ((YEAR(GETDATE()) - 1900) * 12) + @Month - 1, @Day - 1)
IF (@Result < GETDATE())
    SET @Result = DATEADD(year, 1, @Result)

SELECT @Result