title: SQL - Inconsistent datetime conversion issue
body: <p>I am hoping someone can give me an explanation the the following issue involving converting a varchar to datetime.</p>
  
  <p>The code below works:</p>
  
  <pre><code>SELECT
      mt.matter_code,
      ud.uds_type,
      ud.group_no,
      ud.ud_field##2,
      convert(datetime,ud.ud_field##2,103) hearingDate
  FROM dbo.matdb mt
      INNER JOIN dbo.matdb_add_in ad
              ON mt.mt_int_code  = ad.mt_int_code
              AND ad.add_in_code = 'OUTAA'
      INNER JOIN dbo.uddetail ud
              ON convert(varchar,ad.mt_add_in_int_code) = ud.owner_code
              AND ud.parent_code = ad.add_in_code
              AND ud.po_type_char = 'A'
              AND ud.uds_type = 'LPR'
              --AND ud.uds_type IN (SELECT s FROM dbo.split(',','LPR'))
  WHERE mt.mt_type   = 'MATA'
  AND mt.matter_code = '118-1'
  ORDER BY ud.ud_field##2 ASC;
  </code></pre>
  
  <p>However if we substitute the</p>
  
  <pre><code>'AND ud.uds_type = 'LPR'
  </code></pre>
  
  <p>FOR </p>
  
  <pre><code>'AND ud.uds_type IN (SELECT s FROM dbo.split(',','LPR'))'
  </code></pre>
  
  <p>I get the following conversion error message:
  'Conversion failed when converting datetime from character string.'</p>
  
  <p>Both lines of code return the EXACT same results when not using a conversion. However as soon as the conversion is introduced when using dbo.split() I get the error message mentioned above.</p>
  
  <p>Am I missing some kind of reference or is this a bug of sorts?</p>
  
  <p>EDIT:
  dbo.split function</p>
  
  <pre><code>USE [Vfile_Dev]
  GO
  
  /****** Object:  UserDefinedFunction [dbo].[Split]    Script Date: 02/18/2016 13:28:12 ******/
  SET ANSI_NULLS ON
  GO
  
  SET QUOTED_IDENTIFIER ON
  GO
  
  CREATE FUNCTION [dbo].[Split] (@sep char(1), @s varchar(512))
  RETURNS table
  AS
  RETURN (
      WITH Pieces(pn, start, stop) AS (
        SELECT 1, 1, CHARINDEX(@sep, @s)
        UNION ALL
        SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
        FROM Pieces
        WHERE stop &gt; 0
      )
      SELECT pn,
        SUBSTRING(@s, start, CASE WHEN stop &gt; 0 THEN stop-start ELSE 512 END) AS s
      FROM Pieces
    )
  GO
  </code></pre>
  
  <p>EDIT: If anyone is interested, I have provided a solution below which was made possible thanks to Ivan. Additional checks were made on ud.ud_field##2 to ensure that there were no blanks and the dbo.split results are now inserted into a table variable.</p>
  
  <pre><code>INSERT INTO @screenTable
  SELECT s FROM dbo.split(',','LPR,COU');
  
  SELECT
      mt.matter_code,
      ud.uds_type,
      ud.group_no,
      isnull(nullif(ud.ud_field##2,''),'01/01/1990') as hdNorm,
      convert(datetime, isnull(nullif(ud.ud_field##2,''),'1990-01-01 00:00:00.000')) AS hdConverted
  FROM dbo.matdb mt
      INNER JOIN dbo.matdb_add_in ad
              ON mt.mt_int_code  = ad.mt_int_code
              AND ad.add_in_code = 'OUTAA'
      INNER JOIN dbo.uddetail ud
              ON convert(varchar, ad.mt_add_in_int_code) = ud.owner_code
              AND ud.parent_code = ad.add_in_code
              AND ud.uds_type IN (select * from @screenTable)
  WHERE mt.mt_type   = 'MATA'
  AND mt.matter_code = '118-1'
  ORDER BY group_no ASC;
  </code></pre>