CODEWITHSUNDEEP
sqlsql-serverdate-comparison
Massey
Massey

Reputation: 1125

How to find whether a table has a given date range

I want to find out whether a table has rows within the range of dates passed to the query. The following is the data in MYTABLE. I am using SQL Server

DISPLAY_START_DATE      DISPLAY_END_DATE
2022-02-02 00:00:00.000     2022-02-28 00:00:00.000
2022-02-02 00:00:00.000     2022-02-06 10:34:01.653
2022-02-01 00:00:00.000     2022-02-17 00:00:00.000
2022-02-07 00:00:00.000     2022-02-25 00:00:00.000

The following is my query

DECLARE @startdate AS datetime ='2022-02-01' 
DECLARE @enddate AS datetime ='2022-02-10'
SELECT * from MYTABLE mt
WHERE 
(mt.DISPLAY_START_DATE = @startdate and  mt.DISPLAY_END_DATE = @enddate)    OR
(mt.DISPLAY_START_DATE < @startdate and  mt.DISPLAY_END_DATE > @enddate)    OR
(mt.DISPLAY_START_DATE < @startdate and  mt.DISPLAY_END_DATE < @enddate)    OR
(mt.DISPLAY_START_DATE < @startdate and  mt.DISPLAY_END_DATE < @enddate and  
    mt.DISPLAY_END_DATE > @startdate)   OR
(mt.DISPLAY_START_DATE > @startdate and  mt.DISPLAY_END_DATE < @enddate) OR
(mt.DISPLAY_START_DATE > @startdate and mt.DISPLAY_START_DATE < @enddate and 
    mt.DISPLAY_END_DATE < @enddate)

This pulls only the second row corresponding to the following data

DISPLAY_START_DATE      DISPLAY_END_DATE
2022-02-02 00:00:00.000     2022-02-06 10:34:01.653

Upvotes: 1

Views: 457

Answers (2)

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15893

If you want to select all the rows having both the dates within given range you can try below query:

DECLARE @startdate AS datetime ='2022-02-01' 
DECLARE @enddate AS datetime ='2022-02-10'

SELECT * from MYTABLE mt
WHERE 
(mt.DISPLAY_START_DATE between @startdate and @enddate and mt.DISPLAY_END_DATE between @startdate and @enddate)

Or if you want a where condition to select all the rows from the table which have fully or partially in the range

SELECT * from MYTABLE mt
WHERE 
(mt.DISPLAY_START_DATE between @startdate and @enddate and mt.DISPLAY_END_DATE between @startdate and @enddate) or
(mt.DISPLAY_START_DATE <=@startdate and mt.DISPLAY_END_DATE >=@enddate) or
(mt.DISPLAY_START_DATE <=@startdate and mt.DISPLAY_END_DATE between @startdate and @enddate) or
(mt.DISPLAY_START_DATE between @startdate and @enddate  and mt.DISPLAY_END_DATE >=@enddate)

Upvotes: 0

Serg
Serg

Reputation: 22811

Two intervals intersection condition is each interval must start before the other ends:

..
WHERE mt.DISPLAY_START_DATE <= @enddate AND @startdate <= mt.DISPLAY_END_DATE

Upvotes: 1

Related Questions