title: Convert varchar value to date in SQL Server
body: <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>