0

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.

Anirudh
  • 581
  • 5
  • 14
  • 32

1 Answers1

2

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).

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • +1 Wanted to add that `BETWEEN` can also be used for the date comparison. – user2989408 Jan 16 '14 at 21:44
  • @user2989408 . . . Between would work for the date constant (assuming that there is no time component on the dates). It would not work for `getdate()` where `enddate` is today. – Gordon Linoff Jan 16 '14 at 21:45