字符串到日期时间的转换失败，异常是如何找到导致转换的行

Question

我目前正在将一些数据从一个非常旧的数据库（access 2.0）导入到sql server 2012数据库中。我将所有数据（包括日期字段，因为它在2005年1月1日的值为01.01.105）导入varchar字段。

然后我尝试将数据从临时表导入到主表中。在那之前，一切都按预期进行，但我一开始导入数据在主表中（从临时表），我在主表的datetime字段中遇到了一个错误。

我总是在那里收到一条错误消息。

SQLmyemptable中的字段是（Nr-int和datum-varchar（50））mymaintable中的域（orderNo-int和orderDate-Datetime）。

INSERT INTO mymaintable (orderNo, orderDate) SELECT
    Nr,
    TRY_PARSE(CASE WHEN len(datum) = 9
        THEN left(Datum,6) + CAST((CAST(right(Datum, 3) AS int) + 1900) AS varchar(4)) + ' 00:00:00.000'
        ELSE datum  + ' 00:00:00.000'
    END AS DATETIME USING 'de-DE') AS datum
FROM mytemptable;

错误消息

消息6521，级别16，状态1，第1行发生.NET Framework错误在语句执行期间：System.Data.SqlTypes.SqlTypeException：SqlDateTime溢出。必须介于1/1/1753 12:00:00 AM和9999年12月31日下午11:59:59。System.Data.SqlTypes.SqlTypeException：位于位于的System.Data.SqlTypes.SqlDateTime.FromTimeSpan（TimeSpan值）位于的System.Data.SqlTypes.SqlDateTime.FromDateTime（DateTime值）System.Data.SqlServer.Interal.CXVariantBase.DateTimeToSSDate（DateTimedt）

其他信息

我只尝试了具有相同结果的select部分，但另一组数据（20k行中只有1k行）功能没有引起错误。

编辑：我试图用另一篇文章中建议的使用datetime2而不是datetime来解决这个问题，这起到了作用。但是，当我试图通过获取datetime2转换返回null的行来找到这些行时，我没有收到任何结果。这意味着本质上DAtetime转换失败，但datetime2转换根本不会失败。

问题

从我所看到的（因为我已经成功地在数据的不同部分使用了它）来看，这似乎不是一个一般的错误，而是一个与数据相关的错误。所以我的问题是：

有什么方法可以找出问题的根源吗？

（正如我所理解的try_parse，当发生转换错误时，它应该返回null，而不是抛出异常）

感谢

Answer 1

由于datetime2起作用但datetime失败，必须考虑到datetime在日期方面比datetime2更具限制性。因此，通过修改SQL选择，可以很容易地找出错误发生的位置：

 SELECT
    Nr,
    datum
FROM mytemptable
WHERE datum is not null and 
    TRY_PARSE(CASE WHEN len(datum) = 9
        THEN left(Datum,6) + CAST((CAST(right(Datum, 3) AS int) + 1900) AS varchar(4)) + ' 00:00:00.000'
        ELSE datum  + ' 00:00:00.000'
    END AS DATETIME2 USING 'de-DE') < '01.01.1753 00:00:00.000';

添加了"基准不为空"部分，以确保只找到"未知"问题区域。如果上面没有找到任何结果，那么应该使用datetime认为可以接受的最长日期（而不是最短日期）。

这样就可以很容易地找出转换失败的行。