SDU Tools: Nearest Weekday using SQL Server T-SQL

I spend a lot of time doing data conversion work. It's hard to imagine how much of that relates to working with dates and times. A requirement that I had recently was to find the nearest weekday to a given date i.e. find me the closest Thursday to a supplied date.

The NearestWeekday function in our free SDU Tools for developers and DBAs, does this.

It takes two parameters:

@TargetDate date – the date that we're aiming for
@DayOfWeek int – Sunday = 1, Monday = 2, Tuesday = 3, etc.
(values from 1 to 7)

Find out more

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

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.NearestWeekday
(
     @TargetDate date,
     @DayOfWeek int
)
RETURNS date
AS
BEGIN

-- Function:      Returns the nearest nominated day to the target date
-- Parameters:    @TargetDate date - the date that we're aiming for
--                @DayOfWeek int - Sunday = 1, Monday = 2, Tuesday = 3, etc.
-- Action:        Returns the nominated day closest to the date supplied
-- Return:        date
-- Refer to this video: https://youtu.be/YSkWiD5Sfeg
--
-- Test examples: 
/*

SELECT SDU_Tools.NearestWeekday('20201022', 1); 
-- Sunday closest to 22nd Oct 2020
SELECT SDU_Tools.NearestWeekday('20201022', 3); 
-- Tuesday closest to 22nd Oct 2020
SELECT SDU_Tools.NearestWeekday('20201022', 5); 
-- Thursday closest to 22nd Oct 2020

*/
    DECLARE @CorrectDayInSameWeek date 
        = DATEADD(day, 
                  DATEPART(weekday, '19000107') 
                      - DATEPART(weekday, @TargetDate) + @DayOfWeek - 1, 
                  @TargetDate);
    DECLARE @DaysDifferent int 
        = DATEDIFF(day, @TargetDate, @CorrectDayInSameWeek);

    RETURN CASE WHEN ABS(@DaysDifferent) <= 3
                THEN @CorrectDayInSameWeek 
                ELSE CASE WHEN @DaysDifferent < 0
                          THEN DATEADD(day, 7, @CorrectDayInSameWeek)
                          ELSE DATEADD(day, -7, @CorrectDayInSameWeek)
                     END
           END;
END;
GO

CREATE OR ALTER FUNCTION SDU_Tools.NearestWeekday

(

@TargetDate date,

@DayOfWeek int

)

RETURNS date

BEGIN

-- Function: Returns the nearest nominated day to the target date

-- Parameters: @TargetDate date - the date that we're aiming for

-- @DayOfWeek int - Sunday = 1, Monday = 2, Tuesday = 3, etc.

-- Action: Returns the nominated day closest to the date supplied

-- Return: date

-- Refer to this video: https://youtu.be/YSkWiD5Sfeg

-- Test examples:

SELECT SDU_Tools.NearestWeekday('20201022', 1);

-- Sunday closest to 22nd Oct 2020

SELECT SDU_Tools.NearestWeekday('20201022', 3);

-- Tuesday closest to 22nd Oct 2020

SELECT SDU_Tools.NearestWeekday('20201022', 5);

-- Thursday closest to 22nd Oct 2020

DECLARE @CorrectDayInSameWeek date

= DATEADD(day,

DATEPART(weekday, '19000107')

- DATEPART(weekday, @TargetDate) + @DayOfWeek - 1,

@TargetDate);

DECLARE @DaysDifferent int

= DATEDIFF(day, @TargetDate, @CorrectDayInSameWeek);

RETURN CASE WHEN ABS(@DaysDifferent) <= 3

THEN @CorrectDayInSameWeek

ELSE CASE WHEN @DaysDifferent < 0

THEN DATEADD(day, 7, @CorrectDayInSameWeek)

ELSE DATEADD(day, -7, @CorrectDayInSameWeek)

END

END;

Find out more

Latest version of the code

Leave a Reply Cancel reply