title: sql date diff in years - varchar column may be empty or null
body: <p>I need to create an anniversary report. This is the original code;</p>
  
  <pre><code>SELECT  cs.id AS csid ,
          csi.Search_Number AS searchID ,
          csi.date_of_placement AS datePlaced ,
          ui.firstname ,
          ui.lastname ,
          cp.name AS companyname ,
          cp.address_line_1 ,
          cp.address_line_2 ,
          cp.city ,
          cp.state ,
          cp.zip ,
          cp2.name AS currentcompanyname ,
          cp2.address_line_1 AS current_address_line_1 ,
          cp2.address_line_2 AS current_address_line_2 ,
          cp2.city AS current_city ,
          cp2.state AS current_state ,
          cp2.zip AS current_zip
  FROM    client_searches_individuals AS csi
          LEFT JOIN users_info AS ui ON ui.id = csi.individual_number
          LEFT JOIN client_searches AS cs ON cs.id = csi.search_number
          LEFT JOIN companies AS Cp ON cp.id = cs.company_number
          LEFT JOIN companies AS cp2 ON cp2.id = ui.current_company_number
  WHERE   0 = 0 
              &lt;!--- This is for the varchar date field, take away anything with the year selected ---&gt;
                  and csi.date_of_placement not like '#dateformat(attributes.startdate,'yyyy')#%'
  
              &lt;cfif attributes.startdate neq ""&gt;
                  and right(csi.date_of_placement,5) &gt;= '#dateformat(attributes.startdate,'mm/dd')#'
              &lt;/cfif&gt;
              &lt;cfif attributes.enddate neq ""&gt;
                  and right(csi.date_of_placement,5) &lt;= '#dateformat(attributes.enddate,'mm/dd')#'
              &lt;/cfif&gt;
          AND ISNULL(cs.id, 0) &gt; 0
  ORDER BY date_of_placement
  </code></pre>
  
  <p>It's poor code because running it as of 2013/12/31 will give me anniversaries for years not = 2013 and includes rows from 2014.</p>
  
  <p>I've tried using datediff but I'm getting invalid date errors for null or empty date_of_placement (varchar) column.</p>
  
  <p>All I really need is any record that has a placement date older than one year based on today's date.</p>
  
  <p>Using the following code;</p>
  
  <pre><code>WHERE   0 = 0
          AND ( DATEDIFF(year, CAST(csi.date_of_placement AS DATETIME), GETDATE()) ) &gt;= 1
          AND ISNULL(cs.id, 0) &gt; 0
          AND ISNULL(date_of_placement, '') &gt; ''
  </code></pre>
  
  <p>I get;</p>
  
  <blockquote>
    <p>Msg 242, Level 16, State 3, Line 2<br>
    The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.</p>
  </blockquote>
  
  <ul>
  <li>SQL Server 2005</li>
  <li>CF 9</li>
  </ul>
  
  <p>I did a query to correct date_of_placement. Then a second query or the first query to select anything a year old or older.</p>
  
  <pre><code>SELECT  *
  FROM    placedIndGoodDates
  WHERE   0 = 0
          AND DATEDIFF(year, datePlaced, '#asofdate#') &gt;= 1
  ORDER BY date_of_placement
  </code></pre>
  
  <blockquote>
    <p>Query Of Queries syntax error.</p>
    
    <p>Encountered "DATEDIFF ( year. Incorrect conditional expression, Expected one of [like|null|between|in|comparison] condition,</p>
  </blockquote>
  
  <p>I'm missing something fundemental.</p>