问题描述:

I have a table that contains details on charitable donations and the date they were added to the database.

I am trying to write a query that will display the next date after today that will be a three month anniversary of the record being added to the database.

Given a date in the past, I want my query to return the next date in the future that is a multiple of 3 months from the original date i.e. the three month anniversary.

See below for some examples of expected output given todays date.

Date | Expected output

1st Feb 2015 | 1st May 2015,

1st Sep 2014 | 1st Jun 2015

网友答案:

Create a function that returns your looped date, then call it from your table values. There is probably a fancier/faster way to do it, but this would get you what you need.

CREATE FUNCTION [dbo].[fn_Get3MonthAnniv] (@DateVal DATETIME)
RETURNS DATETIME
AS
BEGIN
    DECLARE @LoopDate DATETIME = @DateVal
    DECLARE @Today as datetime = CONVERT(Date,GETDATE())

    While @loopDate<@today
    BEGIN
        SET @loopDate=DATEADD(Month,3,@loopDate)
    END


    RETURN @loopDate

END
GO

 CREATE TABLE #dates (DateVal DATETIME)

INSERT #dates (DateVal)
VALUES ('1/1/2014')
    ,('1/1/2015')

SELECT DateVal
    ,[dbo].[fn_Get3MonthAnniv](DateVal)
FROM #dates

DROP TABLE #dates
网友答案:

You can use the DATEADD function for this task. I use @today variable only for example.

DECLARE @threemonth datetime, @today datetime
SET @today = GETUTCDATE()
SELECT @threemonth = DATEADD(month, 3, @today)
相关阅读:
Top