sql server 2005 - SQL . The SP or function should calculate the next date for friday

Question

I need to write a store procedure that will return a next friday date on a given date? for example - if the date is 05/12/2011, then it should return next friday date as 05/13/2011. If you pass, 05/16/2011, then it should return the date is 5/20/2011 (Friday). If you pass friday as the date, then it should return the same date.

Answer 1

I'd make this a scalar UDF as it is easier to consume the output.

CREATE FUNCTION dbo.GetNextFriday(
@D DATETIME
)
RETURNS DATETIME 
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS
BEGIN
RETURN DATEADD(DAY,(13 - (@@DATEFIRST + DATEPART(WEEKDAY,@D)))%7,@D)
END