Find alternate week day
My co-worker had a task of finding minimum alternate week day as of today for a given date and day. Consider these data
- Date : 2009-12-22
- day : 5 (it denotes Thursday)
It means the event was scheduled on 2009-12-22 which should run on every alternate Thursday starting from 2009-12-24 (The first Thursday on or after the scheduled date). The task is finding the minimum of current available day Here is my solution
declare @date datetime, @day int
select @date='2009-12-22',@day=5
select
min(dateadd(day,14*(number),date))
from
(
select dateadd(day,@day-datepart(weekday,@date),@date) as date
) as t,master..spt_values
where type='p' and dateadd(day,14*(number),date)>=getdate()