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.
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.
No comments:
Post a Comment