Tuesday, June 30, 2009

Date function that determines date range based on weekday

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

No comments:

Split the String values with a special character in MS Flow to convert this into Array

 Many times we have a requirement to prepare the Mailing address for some of the documents, suppose there are Address Line1, Address Line2, ...