asked on

project future date based on past...ms sql server 2005

If I have a date....based in the past...and its on a tuesday...based on a current date...
I need to get the next weekday date in this form:

Past: 2011-02-11 00:00:00 ----Tueday
Current: 2011-02-21 00:00:00
Next reoccuring weekday: 2011-02-24 00:00:00 ---next tuesday after current date...

---another example--hypothetical;

Past: 2011-01-11 00:00:00 ---- Monday
Current: 2011-01-21 00:00:00
Next reoccuring weekday: 2011-01-26 00:00:00 ---next Monday after current date...

GlobaLevel

ASKER

return in a varaible if possible..@next_weekday_date

Ephraim Wangoya

declare @DateNext datetime

set @DateNext = DATEADD(DAY, 7, '2011-02-11 00:00:00')
while @DateNext < GETDATE()
set @DateNext = DATEADD(DAY, 7, @DateNext)

select @dateNext

ASKER CERTIFIED SOLUTION

Ephraim Wangoya

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

GlobaLevel

ASKER

Im not sure that is working quite right....

cyberkiwi

The question has some issues

Past: 2011-02-11 00:00:00 ----Tueday
Next reoccuring weekday: 2011-02-24 00:00:00 ---next tuesday after current date...
(13 days apart...)

Past: 2011-01-11 00:00:00 ---- Monday
Current: 2011-01-21 00:00:00
Next reoccuring weekday: 2011-01-26 00:00:00 ---next Monday after current date...
(25 days apart...)

Is the question supposed to be valid?

You can use this from a table

select NextDate = convert(datetime, datediff(d,0,getdate()) + 6
                                  - datediff(d, pastdate, getdate()-1) % 7)
from sometable

Open in new window

Or from variables

select convert(datetime, datediff(d,0,@current) + 6
                       - datediff(d, @past, @current-1) % 7)

Open in new window

cyberkiwi

Given the data

past = Tues
current = Tues

The previous question returns Next = current (already Tues). The below will return Tues next week.

select convert(datetime, datediff(d,0,getdate()) + 7 
                       - datediff(d, pastdate, getdate()) % 7)
from past

Open in new window