Generating a published data based on created date

Transact-SQL
1

I need to write a procedure that will be made into a trigger so that when an advertisement is ordered, a record will be created in a table that includes the "PublishDate" of when the ad will be ran.

The requirements are fairly simple, but having trouble wrapping my head around how to generate the proper date. In short, the ad will run the Friday of the following week, based on a MON-SUN week. IE, if the add is ordered today, Apr 27th, the date I need to populate is May 5th. Same for any ordered on the 24-30. When it rolls over to Monday May 1st, the date will change to May 12th and so forth.

Any ideas on how to generate that based on the created date (which can be a getdate)? Thanks!

2

This should do it:

SELECT DATEADD(DAY, -DATEDIFF(DAY, 0, GETDATE()) % 7 + 11, GETDATE())