Example select dbo.DAYSEARCH('monday',2,-1,getdate())
CREATE FUNCTION dbo.DAYSEARCH(@day_name VARCHAR(9), @step_count INT, @direction smallint,@dt DATETIME)
RETURNS datetime
AS
BEGIN
/*
Returns a date based upon criteria to find a specific day-of-week
for a specific number of "steps" forward or backward.
For instance, "last Wednesday" or "two Thursdays from today".
@day_name = day of week to find ie. Monday, Tuesday...
@step_count = number of iterations back for a specific day:
--------> "1 Last Monday " = 1
--------> "3 Thursdays from now" = 3
@direction:
--------> -1 if Past
--------> 1 if Future
*/
--declare @day_name VARCHAR(9), @step_count INT, @direction smallint
DECLARE @daysearch datetime
DECLARE @counter smallint
DECLARE @hits smallint
DECLARE @day_name_calc VARCHAR(9)
SELECT @counter = @direction
SELECT @hits = 0
WHILE @hits < @step_count
BEGIN
SELECT @day_name_calc = DATENAME(weekday , DATEADD(d, @counter, @dt))
IF @day_name_calc = @day_name
BEGIN
SELECT @hits = @hits + 1
SELECT @daysearch = DATEADD(d, @counter, @dt)
END
SELECT @counter = (@counter + (1 * @direction))
END
RETURN @daysearch
END