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)