title: Using BETWEEN in SQL CASE where clause
body: <p>I Have a form, where the user pulls a report.
  In the form they can choose a start date and an end date, or pass through a null for both values. If they choose null, it returns all records where effectivedate &lt; GETDATE()
  The CASE statement doesn't seem to like between, nor does it like '&lt;' operators</p>
  
  <p>Here is my script</p>
  
  <pre><code>SELECT * FROM tbReport
  WHERE 
      EffectiveDate 
      CASE 
          WHEN (@StartDate IS NOT NULL AND @EndDate IS NOT NULL) 
          THEN BETWEEN (@StartDate AND @EndDate)          
           ELSE 
                    THEN &lt; GETDATE()
  
      END
  </code></pre>