The UDF is as follows
CREATE FUNCTION dbo.GetLatestWeekDay
(
@DateValue datetime,
@WeekDay int –0-Monday,1-Tuesday,2-Wednesday,3-Thursday,..,6-Sunday
)
RETURNS datetime
AS
BEGIN
DECLARE @LatestWeekDayDate datetime
SET @LatestWeekDayDate= DATEADD(dd,DATEDIFF(dd,-53690 + @WeekDay,@DateValue)/7 * 7,-53690 + @WeekDay)
RETURN (@LatestWeekDayDate)
END
The above UDF can be called as below
SELECT dbo.GetLatestWeekDay(‘20130222’,2) — Gives you the latest Wednesday before 22 Feb 2013
Output will be as follows
The explanation of the logic used in the above UDF can be found in the blog post below
http://visakhm.blogspot.in/2012/07/generate-datetime-values-from-integers.html
Hope you will find the above function useful. Do let me know the feedback.
In case you need more clarification on anything, leave a comment and I’ll revert.