Determine Day of Week in SQL Server

drop function if exists dbo.IsMonday;
go

create function dbo.IsMonday (@date date)
    returns bit
as
begin
    declare @_date date;

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

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

    -- NOTE: Assumes @@datefirst is set to 7 (Sunday), so datepart() 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) = 2
    begin
        return 1;
    end;

    return 0;
end;
go

-- --------------------------------------------------------------------
-- Examples
-- --------------------------------------------------------------------

declare @monday date = convert(date, '2021-08-16');
declare @tuesday date = convert(date, '2021-08-17');

select dbo.IsMonday(null) as NullIsNotMonday; -- assert(0)
select dbo.IsMonday(N'') as EmptyStringIsNotMonday; -- assert(0)
select dbo.IsMonday(@monday) as MondayIsMonday; -- assert(1)
select dbo.IsMonday(@tuesday) as TuesdayIsNotMonday; -- assert(0)
select dbo.IsMonday(convert(datetime, @monday)) as ConvertedDateTimeMondayToDateMondayIsMonday; -- assert(1)