Check if Date Occurs on Day of Week

drop function if exists dbo.OccursOnDayOfWeek;
go

create function dbo.OccursOnDayOfWeek(
    @date date,
    @dayOfWeekName varchar(9)
)
returns bit
as
begin
    -- ==============================================================================================
    -- Name:
    --   dbo.OccursOnDayOfWeek
    --
    -- Description:
    --   SQL Server function for determining if a date occurs on a particular day of the week.
    --
    -- Kind:
    --  User defined scalar function
    --
    -- Params:
    --   @date           The date to check.
    --   @dayOfWeekName  The day of week name.
    --                   Accepted values are: Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday
    --
    -- Return values:
    --   > Returns a 1/true bit value if the @date param occurs on the @dayOfWeekName param.
    --   > Returns a 0/false bit value if @date param does not occur on the @dayOfWeekName param.
    --   > Returns null if the @date param is null or equal to SQL's default date (1900-01-01).
    --   > Returns null if the @dayOfWeekName param is not a valid day name. See accepted values for @dayOfWeekName in Params definition above.
    --
    -- Examples:
    --   select dbo.OccursOnDayOfWeek('2021-08-21', 'Saturday');
    --   >> 1
    --
    --   select dbo.OccursOnDayOfWeek('2021-08-21', 'Tuesday');
    --   >> 0
    --
    --   select dbo.OccursOnDayOfWeek(null, 'Saturday');
    --   >> null
    --
    --   select dbo.OccursOnDayOfWeek('2021-08-21', 'unknown');
    --   >> null
    --
    --   select dbo.OccursOnDayOfWeek('', 'Saturday');
    --   >> null
    --
    -- Link:
    --  https://jonlabelle.com/snippets/view/sql/check-if-date-occurs-on-day-of-week
    -- ==============================================================================================

    declare @_date date;
    declare @_dayNumber int;

    set @_date = try_convert(date, @date);

    -- handle null or default dates early by returning null
    if (@_date is null or convert(date, '1900-01-01') = @_date)
    begin
        return null;
    end;

    set @_dayNumber =
        case
            when charindex('Sunday', @dayOfWeekName) > 0
                then 1
            when charindex('Monday', @dayOfWeekName) > 0
                then 2
            when charindex('Tuesday', @dayOfWeekName) > 0
                then 3
            when charindex('Wednesday', @dayOfWeekName) > 0
                then 4
            when charindex('Thursday', @dayOfWeekName) > 0
                then 5
            when charindex('Friday', @dayOfWeekName) > 0
                then 6
            when charindex('Saturday', @dayOfWeekName) > 0
                then 7
            else null
        end;

    if @_dayNumber is null
    begin
        return null;
    end;

    -- NOTE: Assumes @@datefirst is set to 7 (Sunday), so datepart(weekday, <date>) would 1 (for Sunday), 2 (for Monday), 3 (for Tuesday), etc., etc.
    -- See: https://docs.microsoft.com/en-us/sql/t-sql/statements/set-datefirst-transact-sql
    if datepart(weekday, @_date) = @_dayNumber
    begin
        return 1;
    end;

    return 0;
end;
go