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()