title: selecting records from a table where a given date falls between 2 dates (columns) from a table in SQL Server
body: <p>EDIT ----------------- </p>
  
  <p>I'm going to try to clarify.</p>
  
  <p>I have this table:</p>
  
  <p><img src="https://i.imgur.com/YJBOOEc.png"></p>
  
  <p>A user will input a start date and an end date. </p>
  
  <p>Let's say the user inputs 2011-10-21 for the start and 2011-12-18 for the end.</p>
  
  <p>This will bring back all the records:</p>
  
  <p><img src="https://i.imgur.com/YJBOOEc.png"></p>
  
  <p>But what if the user inputs 2011-10-22 and 2011-12-18?</p>
  
  <p>The 2011-10-22 is BETWEEN the date range of the FIRST ROW 2011-10-21 (start) &amp; 2011-10-23 (end).</p>
  
  <p>SINCE IT IS BETWEEN THE DATE RANGE OF THE FIRST RECORD, I WANT to return that row as well. I DO NOT WANT TO EXCLUDE IT.</p>
  
  <p>So if if I run a query like this:</p>
  
  <p>select * from table where PayPeriodStart between '2011-10-22' and '2011-12-18'</p>
  
  <p>I WANT to see this:</p>
  
  <p><img src="https://i.imgur.com/YJBOOEc.png"></p>
  
  <p>and NOT this:</p>
  
  <p><img src="https://i.imgur.com/AkQzxsO.png"></p>
  
  <p>PLEASE NOTE... the user can enter in any start or end date and it'll be at least 1 week.</p>
  
  <p>So they can pick 2011-11-09 (start) and 2011-12-04 (end) for example. </p>