converting multiple date formats to date in sql server

Question

I have requirement to convert multiple date types input entered by users to standard date format i.e

Input Date Format from old table is of Varchar data type and can be either of DD/MM/YYYY or DDMMYYYY or DDMMYY formats (manually entered by users)

Example: 08/10/2013 or 08102013 or 081013

And my requirement is to convert this to standard date format, how can I achieve this ?

Any help would be appreciated

Answer 1

SQL Fiddle

MS SQL Server 2008 Schema Setup:

create table YourTable(DateCol varchar(20))

insert into YourTable values ('08/10/2013'), ('08102013'), ('081013')

Query 1:

select
  case len(DateCol)
    when 10 then convert(date, DateCol, 103)
    when 8 then convert(date, stuff(stuff(DateCol, 5, 0, '/'), 3, 0, '/'), 103)
    when 6 then convert(date, stuff(stuff(DateCol, 5, 0, '/'), 3, 0, '/'), 3)
  end
from YourTable

Results:

|   COLUMN_0 |
|------------|
| 2013-10-08 |
| 2013-10-08 |
| 2013-10-08 |