CODEWITHSUNDEEP
sql-servert-sql
Philip
Philip

Reputation: 2628

Return rows based off specific date

I have the below sample data.

How can I return the correct rows depending on the date?

For example, if the date is :

20th August - only record IDs 2 and 4 would return

2nd August - only record IDs 1 and 3 would return

8th August - only record IDs 1 and 2 would return

15th July - only record IDs 3 would return

Create table #temp
(
    ID int,
    StartDate datetime,
    EndDate datetime NULL
)

insert into #temp
(
    ID,
    StartDate,
    EndDate
)
select
    1,
    '01 Aug 2019',
    '12 Aug 2019'
union all
select
    2,
    '04 Aug 2019',
    NULL
union all
select
    3,
    '01 Jul 2019',
    '03 Aug 2019'
union all
select
    4,
    '14 Aug 2019',
    NULL

Upvotes: 1

Views: 48

Answers (3)

Shivani
Shivani

Reputation: 151

select * 
from #temp
where @searchdt >= StartDate 
    and (@searchdt <= EndDate OR EndDate is null)

Upvotes: 1

Zohar Peled
Zohar Peled

Reputation: 82524

Here's one sargable way to do it:

SELECT ID,
       StartDate,
       EndDate
FROM #temp
WHERE StartDate <= @SearchDate
AND (EndDate IS NULL OR EndDate > @SearchDate)

Upvotes: 1

mkRabbani
mkRabbani

Reputation: 16908

You can try this below logic-

DECLARE @DT DATE = '20190802'

SELECT * FROM #temp
WHERE (@DT>= StartDate AND @DT <= EndDate)
OR  (EndDate IS NULL AND @DT >= StartDate)

Upvotes: 1

Related Questions