CODEWITHSUNDEEP
sqlt-sql
miltenburger
miltenburger

Reputation: 91

Check date field between 2 other date fields

I'm trying to check if a date is between two other dates. But it doesn't seems to work.

I've created a simple case statement:

CASE
WHEN date1 BETWEEN date2 AND date3
THEN 'Yes'
ELSE 'No'
END

But it gives me no all the time, and yes i'm sure there are some dates between date2 and date3. Am i missing something?

Upvotes: 0

Views: 178

Answers (3)

Ronen Ariely
Ronen Ariely

Reputation: 2434

Good day,

There is no problem in using the format which you provided. Unfortunately, without the ability to read-minds and since you did not provided any sample data and requested result, we have no idea why the result of "no all the time" does not fit you. The only thing you need to understand what "BETWEEN" means

Here is full example which works well, using the format you provided

DROP TABLE IF EXISTS T
GO
CREATE TABLE T(
    id int,
    DT1 DATE,
    DT2 DATE,
    DT3 DATE
)

INSERT T(id, DT1, DT2, DT3) values 
(1,'2005-01-01','2001-01-01','2019-01-01'),
(2,'2000-01-01','2001-01-01','2019-01-01'),
(3,'2019-01-01','2019-01-01','2019-01-01')
GO

SELECT * FROM T
GO

SELECT 
    CASE
        WHEN DT1 BETWEEN DT2 AND DT3
        THEN 'Yes'
        ELSE 'No'
    END 
FROM T

You can notice that the first row has '2005-01-01' is bigger than '2001-01-01' and smaller than '2019-01-01' = therefore it fits and the result is "yes"

The second row has '2000-01-01' which is smaller than '2001-01-01' = therefore it NOT fits and the result is "no"

Third row has '2019-01-01' which is bigger than '2001-01-01' and same value as '2019-01-01' = therefore it fits and the result is "yes"

Upvotes: 1

Joakim Danielson
Joakim Danielson

Reputation: 52088

An alternative to CASE is IIF but you still do the same comparison 

 IIF(date1 between date2 AND date3, 'Yes', 'No')

or without BETWEEN

IIF(date1 >= date2 AND date1 <= date3, 'Yes', 'No')

Upvotes: 2

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32011

I would prefer >= and <= than the between

CASE
WHEN date1>=date2 AND date1<=date3
THEN 'Yes'
ELSE 'No'
END

Upvotes: 3

Related Questions