May 10, 2011 at 2:23 pm
I have a scenario where I have an input date and I need to 3 months to this date on a rolling basis.
Example:
Declare @dt datetime
Set @dt = '04/07/2010'
I need to return the 7th of every 3rd month.
so the next value should be 7/07/2010 and then 09/07/2010
the input date will not change for each user but is different per user.
So if I look at today 05/10/2011 I would expect to get a result of 07/07/2011.
if today was 06/02/2011 I would still expect to get 07/07/2011. ye if today was 07/07/2011 then I would expect to get 09/07/2011.
Any help on this would be appreciated.
Running SQL Server 2005 standard
May 10, 2011 at 2:35 pm
3rd month from May is July and from April it is June?
So if today = 7-Apr-2011 (4/7/11) then the next entry should be 7-June-2011 ( 6/7/2011) ?? your examples show differently.. can you please clarify.?
May 10, 2011 at 2:36 pm
ok i think this might help you visualize:
/*
InputDate StartOfMonth AS StartOfThreeMonths TargetDate
2010-04-17 00:00:00.000 2010-04-01 00:00:00.000 2010-07-01 00:00:00.000 2010-07-07 00:00:00.000
*/
Declare @dt datetime
Set @dt = '04/17/2010'
SELECT
@dt As InputDate,
--first day of that input Date's month:
DATEADD(mm, DATEDIFF(mm,0,@dt), 0) AS StartOfMonth,
--Add three months to THAT
DATEADD(mm,3,DATEADD(mm, DATEDIFF(mm,0,@dt), 0)) AS StartOfThreeMonths,
--add 6 days to THAT to get the 7th
DATEADD(dd,6,DATEADD(mm,3,DATEADD(mm, DATEDIFF(mm,0,@dt), 0))) As TargetDate
Lowell
May 10, 2011 at 2:51 pm
Thanks, that is the basis of what I'm trying to do.
Expanding on your example, if today is 08/05/2011 I would expect a targetdate =09/07/2011
on every 3rd month, the date should forward another 3 months.
In essence 07/07/2011 would be the new input but I need to dynamically calculate this based on the original input date of 04/07/2010 that will never change.
I've tried using a calendar table but was unsuccessful. Also tried CTE but also unsuccessful
Here's how I was using the calendar table. Day, month and year fields were me trying to visualize how to use it. I just can't seem to pick out the correct date and obviously in this query I get the max recursion error
declare @dt smalldatetime,@year smallint, @day tinyint,@mnth tinyint
set @dt = '2010-04-07'
set @year = datepart(year,@dt)
set @day = datepart(day,@dt)
set @mnth = datepart(month,@dt)
;
with cte(expires,year,day,mnth) AS
(Select @dt as expires,@year as y,@day as day,@mnth as mnth
union all
select c.dt ,y,d,m
from calendar c
inner join cte
on c.d= cte.day
where c.y >= @year
and c.dt >= getdate()
)
select *
from cte
May 10, 2011 at 2:58 pm
Try this:
DECLARE @TodaysDate DATETIME
SELECT @TodaysDate = DATEADD ( MM , DATEDIFF( MM , 0, GETDATE() ) ,6)
; WITH Calendar_Months ( N ) AS
(
SELECT 0
UNION ALL SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 9
UNION ALL SELECT 10
UNION ALL SELECT 11
)
SELECT DATEADD ( MM , (N*2) , @TodaysDate) AS [7thDayEvery3rdMonthFromThisMonth]
FROM Calendar_Months
WHERE N < (( 12- MONTH(@TodaysDate) ) / 2 ) + 1
{Edit : Removed hardcoding of the date value}
May 10, 2011 at 3:21 pm
Thanks again for your help with this. There's one piece I left out
there's actually 2 input dates.
Declare @StaticDate datetime, @Today datetime
Set @StaticDate = '04/07/2010'
Set @today = getdate()
So the 3 month interval is based on the @StaticDate
04/07/2010, 07/07/2010, 09/07/2010 etc...
Depending on What @today is will depend on when the 3 month interval changes.
Sticking with the 2010 example (Which the year probably doesn't matter but more importantly the month except when crossing years) ...
If @today = 05/06/2010 then the result should be 07/07/2010
If @today = 07/07/2010 then the result should be 09/07/2010
if @today = 09/01/2010 then the result should still be 09/07/2010
Yet if @today = 09/07/2010 or any date between 09/07/2010 and 11/06/2010 then the result should be 11/7/2010
Only when you cross the day value and the 3 month interval does it then go forward another 3 months.
It's basically like a renewal for every 3 months based on the @StaticDate
I hope this helps clarify and thanks again for the help
May 10, 2011 at 3:43 pm
mistake in the example. 09/07/2010 should be 10/07/2010
and 11/07/2010 should be 01/07/2011
May 11, 2011 at 7:21 am
Thanks for all the input. I think I figured it out. If you have better suggestions, I'm open but I believe it is working.
declare @dt smalldatetime,@day tinyint,@mnth tinyint,@currentyear smallint,@today datetime,@next3 datetime
set @dt = '2010-04-07' --Static Date
set @day = datepart(day,@dt)
set @mnth = datepart(month,@dt)
set @currentyear = datepart(year,getdate())
/*If you change @today to various dates, you should get the next 3rd month nased on 04/07/2010
The only months it should return in this example are 04, 07, 10, 01
in this example when @today = getdate(), the result is 07/07/2011. If you change @today = 11/01/2011 then the result is 01/07/2012
*/
set @today = getdate() --Variable date
set @next3 = dateadd(month,3,@today) -- To limit results
; WITH cte (DT) AS
(
SELECT cast(cast(@mnth as char(2)) + '/' + cast(@day as char(2)) + '/' +cast(@currentyear as char(4)) as datetime)
UNION ALL
select dateadd(month,3,DT)
from cte
where DT <= @next3
)
select top 1 DT
from cte
where DT > @today
return
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy