Converting NVARCHAR to SMALLDATETIME on SQL SERVER 2008

I'm currently working on updating company database in which on one of the tables the CreateDt is in the nvarchar(20) type and stores dates in the following ways:

12 May 12
29/03/2011
9/24/2012
29/01/2001 08:51:56

There are 17,000 rows.

So I'm currently working on code in which I will be able to convert these dates all into the one format such as DD-MM-YYYY.

However I'm struggling to find anything suitable.

So far I have tried:

WITH CreateDt1
AS
(
    SELECT '14 DECEMBER 12' AS CreateDt1
    UNION ALL
    SELECT '13/10/2005'
    UNION ALL
    SELECT '12/14/2012'
    UNION ALL
    SELECT '24/05/2002 09:28:58' 
    UNION ALL
    SELECT '28/02/2011'
)
SELECT
    CreateDt1,

    CASE WHEN ISDATE(CreateDt1) = 1

    THEN CAST(CreateDt1 AS datetime)

    ELSE 

        CASE WHEN SUBSTRING(CreateDt1, 3, 1) = '/'
        THEN 
            CASE WHEN ISDATE(SUBSTRING(CreateDt1, 4, 2) + '/' + LEFT(CreateDt1, 2) + '/' + RIGHT (CreateDt1, 4)) = 1
                THEN CAST(SUBSTRING(CreateDt1, 4, 2) + '/' + LEFT(CreateDt1 , 2) + '/' + RIGHT (CreateDt1, 4) AS datetime)
            END
        END
    END AS NewDate
FROM fct_Project;

However this returns null values for dates such as 29/01/2001 08:51:56.

Solution

Try add LEFT function in RIGHT to your query in case clause like:

CASE WHEN ISDATE(CreateDt1) = 1

THEN CAST(CreateDt1 AS datetime)

ELSE 

    CASE WHEN SUBSTRING(CreateDt1, 3, 1) = '/'
    THEN 
        CASE WHEN ISDATE(SUBSTRING(CreateDt1, 4, 2) + '/' + LEFT(CreateDt1, 2) + '/' + RIGHT (LEFT(CreateDt1,10), 4)) = 1
            THEN CAST(SUBSTRING(CreateDt1, 4, 2) + '/' + LEFT(CreateDt1 , 2) + '/' + RIGHT (LEFT(CreateDt1,10), 4) AS datetime)
        END
    END
END AS NewDate