title: SQL Server - find people between date range excluding the year
body: <p>I have a table EMPLOYEE containing Employee info as mentioned below:</p>
  
  <pre><code>ID     NAME     DOB
  1      ABC      1974-01-01
  2      BDS      1984-12-31
  3      QWE      1959-05-27
  
  and so on
  </code></pre>
  
  <p>I want to list all the employees whose DOB is in the given range.</p>
  
  <pre><code>select * from EMPLOYEE where DOB BETWEEN '1970-01-01' AND '1980-02-27'
  </code></pre>
  
  <p>I have a filter condition to <strong>'include year in date comparison'</strong>, which when selected <strong>'NO'</strong>, the employee DOB day and DOB month
  only should be considered for comparison. <strong>and not the year.</strong></p>
  
  <p><strong>For example:</strong> If I enter the date range as '1970-01-01' and '1980-02-27' and the filter is selected as 'NO' then it should search for only those employees
  whose DOB is greater than equal to JAN-01 and less than equal to FEB-27.</p>
  
  <p>When selected <strong>'Yes'</strong>, it is simply date range as mentioned in above query.</p>
  
  <p>Here is what I have tried so far:</p>
  
  <pre><code>select * from EMPLOYEE where DOB BETWEEN '1970-01-01' AND '1980-02-27'
  AND MONTH(DOB) &gt;= CASE WHEN 'NO'='NO' THEN MONTH('1970-01-01')
  ELSE MONTH(DOB) END
  AND MONTH(DOB) &lt;= CASE WHEN 'NO'='NO' THEN MONTH('1980-02-27')
  ELSE MONTH(DOB) END
  AND DAY(DOB) &gt;= CASE WHEN 'NO'='NO' THEN DAY('1970-01-01')
  ELSE DAY(DOB) END
  AND DAY(DOB) &lt;= CASE WHEN 'NO'='NO' THEN DAY('1980-02-27')
  ELSE DAY(DOB) END
  </code></pre>
  
  <p>It works when I pass the date range where the FROM date has smaller number month than the TO date month.</p>
  
  <p>For example:
  It doesnt work when I pass the date range as '1970-12-01' to '1980-01-31'.
  It should list the employees whose DOB is in DEC and JAN month.</p>
  
  <p>Need help please.</p>