Skip to content
Advertisement

select smalldatetime between two strings

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 ?

enter image description here

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

Advertisement

Answer

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;
8 People found this is helpful
Advertisement