[SOLVED] Sql server 2008 : Query to search for today's date within overlapping dates

Sql server 2008 : Query to search for today's date within overlapping dates

enter image description here

I have the above table wherein I need to get NoteIndx matching today's date. Just for testing, assume today is 2013/06/02 (Jun 2,2013) and that would get me two records with noteindx 93105 and 104044.

But the correct value here is 104044 as I need to choose the one that has shortest datespan of STRTDATE and ENDDATE.

I am trying with various sql queries but not getting correct result so far.

Can anyone help me with the best possible query to get above results?

Thanks.

Solution

Is this what you want?

select top 1 n.*
from notes n
where cast('2013-06-02' as date) >= strtdate and
      cast('2013-06-02' as date) < enddate + 1
order by enddate - strtdate;

This does not use between because your dates are stored as datetime -- this always introduces the possibility of their being a time portion on the date.

If you want today's date, then use getdate() instead of cast('2013-06-02' as date).