title: SQL Server Arithmetic Overflow Error - converting nvarchar to datetime
body: <p>I'm retrieving data from a view, and one of the columns I'm using is <code>nvarchar(50)</code>, but is only ever <code>N'True'</code> or <code>N'False'</code>, depending on the operation of a related date column in this parent view.</p>
  
  <p>The following code retrieves the record ID and the column I'm looking for, <code>YTD</code>:</p>
  
  <pre><code>SELECT Enquiry_Number, YTD 
  FROM dbo.vw_SalesPO_YTD
  </code></pre>
  
  <p>Output:</p>
  
  <pre><code>ENQ-001 True
  ENQ-002 False
  ENQ-003 True
  </code></pre>
  
  <p>However, I'm unable to filter my results using this <code>YTD</code> column for some reason. If I attempt to do this:</p>
  
  <pre><code>SELECT Enquiry_Number, YTD
  FROM dbo.vw_SalesPO_YTD
  WHERE YTD = N'True'
  </code></pre>
  
  <p>Then it fails with the following error:</p>
  
  <blockquote>
    <p>Arithmetic overflow error converting expression to data type datetime.</p>
  </blockquote>
  
  <p>Which I don't understand because there are no <code>datetime</code> expressions in play in this query. Yes the <code>True</code> or <code>False</code> was determined by comparing datetimes in the parent view, but I don't understand how that might have trickled down to this subquery. Attempting the same thing in the parent view yields the same error - I'm demonstrating it this way for simplicity's sake.</p>
  
  <p>However, performing a similar operation in the <code>SELECT</code> portion of the query works without issues:</p>
  
  <pre><code>SELECT 
      Enquiry_Number,
      YTD,
      CASE 
         WHEN YTD = N'True' THEN 1 ELSE 0 
      END As C
  FROM
      dbo.vw_SalesPO_YTD 
  </code></pre>
  
  <p>Output:</p>
  
  <pre><code>ENQ-001 True 1
  ENQ-002 False 0
  ENQ-003 True 1
  </code></pre>
  
  <p>However, these 1's and 0's inherit the same flaw, where I can't use them in a <code>WHERE</code> clause without getting this datetime error.</p>
  
  <p>I've been searching hard and am not sure how to identify the core issue. I've been reading things about Collations and type precedence, but can't understand why this behaviour is happening. </p>
  
  <p>When I've checked <code>YTD</code> in <code>INFORMATION_SCHEMA.COLUMNS</code>, it confirms that this column is no different from other columns in my table: <code>YTD</code> is <code>nvarchar(50)</code>, using the <code>Latin1_General_CI_AS</code> collation.</p>
  
  <p>Related question: <a href="https://stackoverflow.com/questions/29573774/sql-server-arithmetic-overflow-error-converting-expression-to-data-type-datetime">SQL Server Arithmetic overflow error converting expression to data type datetime</a></p>
  
  <hr>
  
  <h2>The Source of the Problem</h2>
  
  <p>This issue is still unsolved, but if you wish to reproduce it, this code from the parent view must be generating this issue:</p>
  
  <pre><code>CASE WHEN 
  Award_Date &lt;= DATEFROMPARTS(FinancialYear - 1, 11, 1) + GETDATE() - DATEADD(year, DATEDIFF(month, '20161101', GETDATE()) / 12, '20161101')
  THEN N'True'
  ELSE N'False'
  END
  </code></pre>
  
  <p>Yes this looks overly complicated. We're checking the <code>Award_Date</code> against its associated <code>FinancialYear</code>, which runs from November 1st to October 31st. Each record already knows which <code>FinancialYear</code> it's in. The ultimate aim is to compare TODAY's position (2016-11-30) against TODAY last year (2015-11-30), and TODAY the year before (2014-11-30), etc.</p>
  
  <p>So the code takes today's date and combines it with the <code>FinancialYear</code> for the associated record, and spits out whether the record had occurred between the start of its financial year and the <strong>today</strong> of the same year. And it's doing this successfully, but then I can't do anything with the <code>N'True'</code> or <code>N'False'</code> it's producing.</p>