SQL:如何生成给定月份和日期的下一个日期

这样的方法是可行的。它是对你的方法的变体,但它不使用MM/DD/YYYY文字格式,并且不会对错误的输入产生影响(无论好坏)。

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

或者,要创建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

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

your_date_calculated = Year * 10000 + Month * 100 + Day 

给你一个日期,比如20090623。

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

DATEADD

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