SDU Tools: Weekday in same week using SQL Server T-SQL

A while back, I got a request to add a new function to our free SDU Tools for developers and DBAs. The user wanted to be able to find a particular day of the week, in a given week. So for example, if I give you a date of 30th April 2021, what is the Thursday in that week? So we’ve added a new function WeekdayOfSameWeek.

It takes two parameters:

@DayInTargetWeek date - any day that can be used to find the target week @DayOfWeek int - Sunday = 1, Monday = 2, etc.

Note that the function assumes the week starts on a Sunday. I know my Asian friends (and others) consider the week to start on a Monday. It’s easy to change to make it work that way.

The return value is a date.

Find out more

You can see it in action in the main image above, and in the video here. The full current version of the code is also shown below:

https://youtu.be/XHMrwNvqwdQ

You can use our tools as a set or as a great example of how to write functions like these.

Access to SDU Tools is one of the benefits of being an SDU Insider, along with access to our other free tools and eBooks. Please just visit here for more info:

http://sdutools.sqldownunder.com

Latest version of the code

Note: the code might wrap when displayed below.

CREATE OR ALTER FUNCTION SDU_Tools.WeekdayOfSameWeek
(
     @DayInTargetWeek date,
     @DayOfWeek int
)
RETURNS date
AS
BEGIN

-- Function:      Returns the nominated day of the target week
-- Parameters:    @DayInTargetWeek date - any day in the target week
-- @DayOfWeek int - Sunday = 1, Monday = 2, etc.
-- Action:        Returns the nominated day in the same week as the target date
-- Return:        date
-- Refer to this video: https://youtu.be/XHMrwNvqwdQ
--
-- Test examples: 
/*

-- Sunday in week of 22nd Oct 2020
SELECT SDU_Tools.WeekdayOfSameWeek('20201022', 1);

-- Thursday in week of 22nd Oct 2020
SELECT SDU_Tools.WeekdayOfSameWeek('20201022', 5); 

*/
    RETURN DATEADD(day, 
                   DATEPART(weekday, '19000107') 
                       - DATEPART(weekday, @DayInTargetWeek) 
                       + @DayOfWeek - 1, @DayInTargetWeek);
END;
GO

2021-02-19