title: Convert Varchar columns to Datetime
body: <p>I am tasked to calculate the difference in months and fiscal years from a <code>NUMBERS</code> column and a <code>SHIP_DATES</code> column. I have trouble converting date or time from character string in the <code>NUMBERS</code> columns (<code>NUMBER_MONTH</code>), and (<code>NUMBER_FY</code>) before the calculations.</p>
  
  <p>Could someone please look? Basically, the issue I have is I cant take <code>SHIP_DATE_MONTH</code> minus <code>NUMBER_MONTH</code> and <code>SHIP_DATE_FY</code> minus <code>NUMBER_FY</code>.</p>
  
  <ul>
  <li><p>Column <code>NUMBERS</code> is <code>varchar</code>. It is formatted as <code>SSSYYMMFFFFFF</code>, where</p>
  
  <ul>
  <li><code>SSS</code> is the store station,</li>
  <li><code>YY</code> is the fiscal year,</li>
  <li><code>MM</code> is the month, and</li>
  <li><code>FFFFFF</code> is the frequency</li>
  </ul></li>
  <li><p>Column <code>SHIP_DATES</code> is <code>datetime</code>.</p></li>
  <li><p>Column <code>ID</code> is int.</p></li>
  </ul>
  
  <p>Here is my code:</p>
  
  <pre><code>CREATE TABLE #TEMP
  (
    NUMBERS    VARCHAR (20),
    SHIP_DATES DATETIME,
    ID         INT
  )
  
  INSERT INTO #TEMP VALUES ( 'ABC1006000046' , '6/5/2010'  , '123' )
  INSERT INTO #TEMP VALUES ( 'ABC1006000046' , '7/15/2013' , '123' )
  INSERT INTO #TEMP VALUES ( 'CDS0809000059' , '9/8/2008'  , '124' )
  INSERT INTO #TEMP VALUES ( 'CDS0809000059' , '1/31/2013' , '124' )
  
  SELECT SUBSTRING(NUMBERS, 6, 2)     AS NUMBER_MONTH,
         SUBSTRING(NUMBERS, 4, 2)     AS NUMBER_FY,
         DATEPART (MONTH, SHIP_DATES) AS SHIP_DATE_MONTH,
         DATEPART (YEAR, SHIP_DATES)  AS SHIP_DATE_FY,
         ID
  INTO #TEMP1
  FROM #TEMP
  
  --calculate the difference in month and fiscal year
  SELECT DATEDIFF( YEAR  , NUMBER_FY    , SHIP_DATE_FY    ) AS DIFF_YEAR  ,
         DATEDIFF( MONTH , NUMBER_MONTH , SHIP_DATE_MONTH ) AS DIFF_MONTH ,
         ID
  FROM #TEMP1
  </code></pre>
  
  <p>and this is the error message I receive:</p>
  
  <p><code>Conversion failed when converting date and/or time from character string.</code></p>
  
  <p>Please note, here is a converting to datetime approach, but i have not figured out how to make it work yet:</p>
  
  <pre><code>CONVERT(VARCHAR , SHIP_DATES , 101 ) AS 'MM/DD/YYYY'
  </code></pre>