将NVarchar转换为SQL Server 2008上的SmallDateTime

Question

我目前正在尝试更新公司数据库，其中CreateDt在其中一张表中使用了nvarchar(20)类型，并按照以下方式存储日期：

5月12日29/03/20119/24/20122001/29/01 08:51:56

有17,000行。

因此，我目前正在处理代码，其中我将能够将这些日期全部转换为一种格式，例如DD-MM-YYYY。

但是我正在努力寻找任何合适的东西。

到目前为止，我已经尝试过：

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;

但是，这返回了诸如 29/01/2001 08:51:56的日期的零值。

Answer 1

尝试在查询右侧的右添加左功能，例如：

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

Answer 2

我刚刚通过添加几行编辑了您的代码，希望它会有所帮助。请记住，它不能解决10/11和11/10案件的问题。

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 
    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)
                         WHEN ISDATE(SUBSTRING(CreateDt1, 4, 2) + '/' + LEFT(CreateDt1, 2) + '/' + LEFT((RIGHT (CreateDt1, 13)),4) ) = 1 
                         THEN CAST ( (SUBSTRING(CreateDt1, 4, 2) + '/' + LEFT(CreateDt1, 2) + '/' + LEFT((RIGHT (CreateDt1, 13)),4) ) AS DATETIME)
                    END
             END

    END AS NewDate
FROM CreateDt1;