Trying to convert the date of a column to be mmddy

Question

I'm having an issue trying to convert the dates of a column to be a standard MMDDYYYY format.

For example I have a table with data as follows:

ID   |   Date   |   Type   |
----------------------------
1    | 20140308 | License  |
2    | 20140308 | License  |
3    | 48       | License  |

The outcome I want to receive is:

ID   |   Date      |   Type   |
-------------------------------
1    | 3-8-2014    | License  |
2    | 3-8-2014    | License  |
3    | 48(not sure)| License  |

Is this possible when I have garbage data going into the system? I keep getting an error like this when I try to convert the dates into MMDDYYYY.

Msg 241, Level 16, State 1, Line 1 Conversion failed when converting date and/or time from character string. Warning: Null value is eliminated by an aggregate or other SET operation.

Answer 1

What about this....

Test Data

DECLARE @Table TABLE(ID INT,[Date] VARCHAR(8),[Type] VARCHAR(20))
INSERT INTO @Table VALUES
(1,'20140308','License'),
(2,'20140307','License'),
(3,'48'      ,'License')

Query

SELECT ID 
      ,CASE WHEN ISDATE([Date]) = 1
                THEN CONVERT(VARCHAR(10), CAST([DATE] AS DATE), 110)
            ELSE NULL END AS [Date]
      ,[Type]
FROM @Table

Result Set

╔════╦════════════╦═════════╗
║ ID ║    Date    ║  Type   ║
╠════╬════════════╬═════════╣
║  1 ║ 03-08-2014 ║ License ║
║  2 ║ 03-07-2014 ║ License ║
║  3 ║ NULL       ║ License ║
╚════╩════════════╩═════════╝