Wednesday, March 13, 2013

Retrieve latest weekday date from a given date

Recently for one of my projects there was a requirement to get the latest Tuesday from a date value. I had used the below logic for the requirement. I'm converting it to a UDF and posting it here so that people with similar requirement can make use of it.
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
DECLARE @LatestWeekDayDate datetime
SET @LatestWeekDayDate= DATEADD(dd,DATEDIFF(dd,-53690 + @WeekDay,@DateValue)/7 * 7,-53690 + @WeekDay)
RETURN (@LatestWeekDayDate)

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

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.