Oracle-SQL查询在SQL Developer中运行良好，但引发错误ORA-01861 Literal与SSIS中的格式字符串不匹配

Question

我正在尝试将DD-MON-RR转换为oracle中的YYY-MM-DD格式，我写了下面的转换，它在SQLDeveloper中运行良好。

TO\u CHAR（TO\u DATE（DATE，'DD-MON-RR'），'YYYY-MM-DD'）

但是，如果我在SSIS中使用相同的查询，它会抛出与格式字符串不匹配的ORA-01861文本。

非常感谢您的帮助！！

Answer 1

TO\u CHAR（日期，'YYYY-MM-DD'）输出：0258-07-20

这意味着数据库中存储的日期实际上是258年，而不是1958年。

当你这么做的时候

TO_CHAR(TO_DATE(date,'DD-MON-RR'),'YYYY-MM-DD')

您正在使用当前会话NLS日期格式将日期隐式转换为字符串，该格式将为您提供一个类似“20-JUL-58”的字符串。然后使用格式掩码“DD-MON-RR”将其显式转换回日期。

您正在有效地执行以下操作：

TO_CHAR(TO_DATE(TO_CHAR(date,<nls_date_format>),'DD-MON-RR'),'YYYY-MM-DD')

在SQL开发人员会话中，有效地：

TO_CHAR(TO_DATE(TO_CHAR(date,'DD-MON-RR'),'DD-MON-RR'),'YYYY-MM-DD')

因为中间字符串有一个2位数的年份（58），并且有RR元素，所以变成1958年。

当您从SSIS做同样的事情时，会话日期格式似乎有一个4位数的年份，因此它有效地执行了以下操作：

TO_CHAR(TO_DATE(TO_CHAR(date,'DD-MM-YYYY'),'DD-MON-RR'),'YYYY-MM-DD')

现在，中间字符串有一个4位数的年份（0258），RR元素保持为0258。

现在您可以指定中间字符串格式，而不是依赖NLS，这至少在两个会话之间是一致的：

TO_CHAR(TO_DATE(TO_CHAR(date,'DD-MON-RR'),'DD-MON-RR'),'YYYY-MM-DD')

或者将SSIS视为日期而不是字符串，删除外部to_char（）：

TO_DATE(TO_CHAR(date,'DD-MON-RR'),'DD-MON-RR')

还要注意，此转换以午夜值的时间部分结束；如果你的源日期有非午夜时间（根据评论可能不是因为它是出生日期），并且你想保留这些时间，那么你应该在面具中包括时间：

TO_DATE(TO_CHAR(date,'DD-MON-RR HH24:MI:SS'),'DD-MON-RR HH24:MI:SS')

但是这种双重转换对于坏数据来说是一种棘手的解决方法，实际上，您应该修复源表中的数据以获得正确的年份，并查看如何插入值以防止它们再次出错。

根据它们错误的确切方式和原因——可能是不正确或隐式的格式掩码和/或插入过程中意外的NLS设置——您可能需要评估是否可以依赖年份的最后两位数字是正确的并匹配RR元素行为。也许您是否可以依赖月份和日期部分是正确的。