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

Here is my sql example so far. I don't really like it though...

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 2

Here's a solution with PostgreSQL

your_date_calculated = Year * 10000 + Month * 100 + Day

gives you a date like 20090623.

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

Answer 3

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

Answer 4

Here's my version. The core of it is just two lines, using the DATEADD function, and it doesn't require any conversion to/from strings, 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