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
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.