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 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply