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;

Answer 2

It is working for me.

I am guessing the issue is related to the Date/Time settings on your machine.

You may need to check SET DATEFORMAT ...

SET DATEFORMAT (Transact-SQL)

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE  (ID INT IDENTITY PRIMARY KEY, MajDate smalldatetime);
INSERT INTO @tbl (MajDate) VALUES
('2016-11-30 11:23:00'),
('2021-07-07 11:07:00'),
('2021-07-07 11:07:00');
-- DDL and sample data population, end

SET DATEFORMAT mdy;

SELECT ID, MajDate
    , CASE WHEN MajDate BETWEEN '2021-07-01 00:00:00' AND '2021-08-01 00:00:00'
            THEN 'YES'
        ELSE 'NO'
    END AS InRange
    ,IIF(MajDate BETWEEN '2021-07-01 00:00:00' AND '2021-08-01 00:00:00', 'YES', 'NO')AS result
FROM @tbl;

Output

+----+---------------------+---------+--------+
| ID |       MajDate       | InRange | result |
+----+---------------------+---------+--------+
|  1 | 2016-11-30 11:23:00 | NO      | NO     |
|  2 | 2021-07-07 11:07:00 | YES     | YES    |
|  3 | 2021-07-07 11:07:00 | YES     | YES    |
+----+---------------------+---------+--------+