Convert varchar value to date in SQL Server
<p>I'm converting varchar data to date in SQL server.</p>

<p>Table having data like below,</p>

<p><a href="https://i.stack.imgur.com/DombK.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/DombK.png" alt="enter image description here"></a></p>

<p>So it can have NULL value, proper formatted date and can have like <code>19900411</code>and  <code>04221995</code>.</p>

<p>So I have tried something like below, but getting error.</p>

<pre><code>SELECT CASE 
            WHEN ISNULL(CAST(Dob AS VARCHAR), '') = '' THEN NULL
            WHEN LEN(CAST(Dob AS VARCHAR)) = '8' THEN CONVERT(
                     VARCHAR(10),
                     CONVERT(date, RIGHT(Dob, 4) + LEFT(Dob, 2) + SUBSTRING(Dob, 3, 2)),
                     103
                 )
            ELSE CONVERT(VARCHAR, CAST(Dob AS CHAR(8)), 103)
       END
FROM   TableName
WHERE  Dob IS NOT NULL
</code></pre>

<blockquote>
  <p>Msg 241, Level 16, State 1, Line 3 Conversion failed when converting
  date and/or time from character string.</p>
</blockquote>

<p>I wanted to get output as date format <code>MM-dd-yyyy</code> </p>

<p><em>Please help me! Thanks!</em></p>