title: SQL Server 2012 - Select queries leap year
body: <p>I have a little issue with one my queries. The entire query is actually is a little long since it includes a <code>UNION ALL</code>. </p>
  
  <pre><code>SELECT 'ITV' = CASE WHEN tblIntake.StaffID Is Null THEN '&lt;Unknown&gt;' ELSE
  (tblStaffMember.StaffLast + ', ' + tblStaffMember.StaffFirst + CASE WHEN
  tblStaffMember.StaffMI Is Null THEN '' ELSE ' ' + tblStaffMember.StaffMI END) END, 
  tblMember.[LAST], tblMember.[FIRST], 
  'DueDate' = DATEADD(m, 6,CAST(CONVERT(Varchar(10),
  MONTH(tblIntake.EnrollDate)) + '/' + CONVERT(Varchar(10),
  DAY(tblIntake.EnrollDate)) + '/' + CONVERT(Varchar(10),YEAR(GETDATE()))As
   DateTime)), 'Type' = '6 Month Appt' 
  
  From tblIntake LEFT JOIN tblStaffMember ON tblIntake.StaffID =
  tblStaffMember.StaffID LEFT JOIN tblMember ON 
  tblIntake.KEY = tblMember.KEY 
  
  Where tblIntake.UnEnrollDate Is Null AND
  DATEADD(m,6,CAST(CONVERT(Varchar(10),MONTH(tblIntake.EnrollDate)) + '/' +
  CONVERT(Varchar(10),DAY(tblIntake.EnrollDate)) + '/' +
  CONVERT(Varchar(10),YEAR(GETDATE()))As DateTime)) &gt; GETDATE() AND 
  DATEADD(m, 6,CAST(CONVERT(Varchar(10),MONTH(tblIntake.EnrollDate)) + '/' +
  CONVERT(Varchar(10),DAY(tblIntake.EnrollDate)) + '/' +
  CONVERT(Varchar(10),YEAR(GETDATE()))As DateTime)) &lt;= DATEADD(d, 45, GETDATE()) 
  </code></pre>
  
  <p>So, I have this wonderful query. Everything was running okay until a user entered a leap year date for <code>ENROLLDATE</code> in <code>tblIntake</code>. How would I go about fixing it? My other <code>UNION</code> does the same <code>SELECT</code> statements with the exception of for when it does </p>
  
  <pre><code>CONVERT(VARCHAR(10),YEAR(GetDate()-1)) as DateTime &gt; '4th line from the bottom
  CONVERT(VARCHAR(10),YEAR(GetDate()-1)) as DateTime &gt; '2nd line from the bottom
  </code></pre>
  
  <p>EDIT:</p>
  
  <p>Receiving this error when I run the query</p>
  
  <blockquote>
    <p>Msg 242, Level 16, State 3, Line 1<br>
    The conversion of a varchar data type to a datetime data type resulted in an  out-of-range value.</p>
  </blockquote>
  
  <p>I tried running the separate queries, and it seems like I'm only getting the error when I do this..</p>
  
  <pre><code> CONVERT(VARCHAR(10),YEAR(GetDate()-1)) as DateTime &gt; '4th line from the bottom
  CONVERT(VARCHAR(10),YEAR(GetDate()-1)) as DateTime &gt; '2nd line from the bottom
  </code></pre>
  
  <p>After UNION ALL</p>
  
  <p>EDIT 2:</p>
  
  <p>I've been working off Sean's answer which makes more sense. Here is the code that I've got....</p>
  
  <pre><code>SELECT 'CSC' = case when tblIntake.staffID is null Then '&lt;notIndicated&gt;' Else (tblStaffMember.staffLast + ',' + tblStaffMember.StaffFirst + case when tblStaffMember.staffMI is null then '' else ' ' + tblStaffMember.staffMI END)end  
  , tblMember.[Last], tblMember.[First]
  ,'Due' = DateADD(m,6,cast(tblIntake.enrolldate as datetime))
  ,'Type' = '6 Month Review'
  from tblMembEnrollment
  left join tblStaffMember on tblIntake.staffID = tblStaffMember.staffID 
  left join tblMember on tblIntake.SBkey=tblMember.sbkey
  where tblIntake.unEnrollDate is null and dateAdd(m, 6, tblIntake.enrolldate)  &gt; GETDATE()
  and dateadd(m, 6, cast(tblIntake.enrolldate as DateTime))&lt;= DateAdd(d,45,GetDate())
  </code></pre>
  
  <p>The issue I'm seeing is that in the old query, when the code takes apart the EnrollDate into days, months and years for years it used GET DATE, see below in between *** ****</p>
  
  <pre><code>Where tblIntake.UnEnrollDate Is Null AND
  DATEADD(m,6,CAST(CONVERT(Varchar(10),MONTH(tblIntake.EnrollDate)) + '/' +
  CONVERT(Varchar(10),DAY(tblIntake.EnrollDate)) + '/' +
  CONVERT(Varchar(10),***YEAR(GETDATE()))As DateTime))*** &gt; GETDATE()
  </code></pre>
  
  <p>Now it was easier done this way since he did take it apart, how is it that I can do it with my full Enroll date. </p>