select smalldatetime between two strings

Question

DROP TABLE IF EXISTS b;

CREATE TABLE b(
    MajDate smalldatetime
);

INSERT INTO b(MajDate) VALUES
(try_convert(smalldatetime,'2016-11-30 11:23:00')),
(try_convert(smalldatetime,'2021-07-07 11:07:00')),
(try_convert(smalldatetime,'2021-07-07 11:07:00'))

select 
b.MajDate,
CASE WHEN b.MajDate BETWEEN '2021-07-01 00:00:00' AND '2021-08-01 00:00:00'
        THEN 'YES'
    ELSE 'NO'
END AS InRange
From b;

What am I doing wrong ?

Desired Output: Column InRange should contain YES for two last rows.

Answer 1

Try specifying ISO format dates, more than likely your regional settings are having an effect.

If you use YYYYMMDD there is no ambiguity. The following works fine:

select 
b.MajDate,
CASE WHEN b.MajDate BETWEEN '20210701 00:00:00' AND '20210801 00:00:00' THEN 'YES' else 'NO'
END AS InRange
From b;