title: SQL Server 2008 R2 bug or what?
body: <p>I am observing a strange behaviour. The following code that is related to conversion of a substring of <code>nvarchar</code> to <code>tinyint</code> works fine:</p>
  
  <pre><code>DECLARE @s nvarchar(50) = N'Line 1'
  DECLARE @n tinyint = SUBSTRING(@s, 6, 10)
  
  SELECT
          N'Line 1' AS explicit_nvarchar,
          @s AS nvarchar_variable,
          SUBSTRING(@s, 6, 10) AS nvarchar_substring
          , @n AS tinyint_var_converted_earlier
          , CAST(SUBSTRING(@s, 6, 10) AS tinyint) AS cast_sub_var
          , CAST(SUBSTRING(N'Line 1', 6, 10) AS tinyint) AS cast_nvarchar_substr
  </code></pre>
  
  <p>I can observe the following output in the Microsoft SQL Server Management Studio:</p>
  
  <p><img src="https://i.stack.imgur.com/DqlZO.png" alt="enter image description here">. </p>
  
  <p>However, the code that tries to do with the <code>SELECT</code> from the tables fails if the commented line is uncommented:</p>
  
  <pre><code>Select 
      [order].[identifier] As [order_identifier],
      [plan_data].[starts_on] As [from],
      [plan_data].[ends_on] As [to],
      [workplace].[identifier] AS line_identifier,
      SUBSTRING([workplace].[identifier], 6, 10) AS line_no_str
      --, CAST(SUBSTRING([workplace].[identifier], 6, 10) AS int) AS line_no
  From 
      ...
  </code></pre>
  
  <p>With commented line...
  <img src="https://i.stack.imgur.com/JFoil.png" alt="enter image description here"></p>
  
  <p>but with uncommented line (both for conversion to <code>tinyint</code> or <code>int</code>)...</p>
  
  <p><img src="https://i.stack.imgur.com/QKqcC.png" alt="enter image description here"></p>
  
  <p><strong>Update:</strong> Strange. I have added the last line to the WHERE to check whether all lines
  contain the <code>'Line...</code></p>
  
  <pre><code>Where
      [plan].[identifier] = @lPlanIdentifier And
      (
          [plan_data].[starts_on] Between @fromUTC And @toUTC Or
          [plan_data].[ends_on] Between @fromUTC And @toUTC)
      AND LEFT([workplace].[identifier], 4) = N'Line'
  </code></pre>
  
  <p>... and all of a sudden it works also with the uncommented line. How can I discover what causes the problem?</p>
  
  <p><strong>Update2:</strong> I should have followed the <a href="https://stackoverflow.com/a/14468720/1346705">Hamlet Hakobyan's answer</a> exactly as it was published. When looking inside the table, I can see:</p>
  
  <p><img src="https://i.stack.imgur.com/VJj6w.png" alt="enter image description here"></p>