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.

While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in #1 above. (4) Your SQL Server version (SELECT @@version;). All within the question, no images. — Yitzhak Khabinsky, Aug 31 '21 at 14:37
If I set my language to [American] `ENGLISH` or my date format to `mdy` your SQL works fine. If I remain English (`BRITISH`), it doesn't. But, if I use an unambiguous date format, it works fine again. Seems the problem if your ambigous dates. [db<>fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=0547dd9dd39f2b30dda9699d71f797d5) — Thom A, Aug 31 '21 at 14:59
Beware, the format `yyyy-MM-dd hh:mm:ss` is not culture invariant with `DATETIME` and `SMALLDATETIME`, so if you are running in your French regional settings, then `2021-07-01 00:00` is probably being picked up as 7th January. [Example on db<>fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=f0584db47d7727efd955b2eb41cd0a22) — GarethD, Aug 31 '21 at 15:01

Yitzhak Khabinsky · Answer 1 · 2021-08-31T15:07:36.720

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    |
+----+---------------------+---------+--------+

score 1 · Accepted Answer · answered Aug 31 '21 at 14:58

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;

select smalldatetime between two strings

2 Answers2