To_char and To_date

Question

我正在努力解决一个包含 varchar2 列作为 MON-YY 格式日期的报告

我想做的是将该列的值转换为日期，以便我可以对其进行一些处理，例如使用> 或<甚至在和设置两个日期之间进行过滤>

我的列名称：BAL。PERIOD_NAME 以下是条件提示错误 ORA-01843：不是有效的月份

AND TO_DATE(BAL.PERIOD_NAME,'MON-YY')  > TO_DATE('FEB-18','MON-YY')

提前致谢

Answer 1

确保对要转换的日期使用正确的格式掩码。

工程：

select  TO_DATE('feb-18','MON-YY') from dual

抛出"不是有效的月份"错误：

select  TO_DATE('02-18','MON-YY') from dual

Answer 2

您可以使用递归 CTE 检查表中的 invalif 月份名称。

WITH cte(month)
AS
(
SELECT 1 month
FROM dual
UNION ALL
SELECT month + 1 month
FROM cte
WHERE month + 1 <= 12
)
SELECT *
FROM bal
WHERE substr(period_name, 1, 3) NOT IN (SELECT to_char(to_date(month, 'MM'), 'MON')
FROM cte);

Answer 3

将日期值存储为字符串(如'FEB-18'(确实是一个设计缺陷。您应该考虑迁移到DATE(或TIMESTAMP(数据类型。

• 你从来没有听说过2000年的问题吗？好吧，我假设当IT界的每个人都在谈论Y2K问题时，许多现在的软件开发人员还是孩子。
• 字符串可能以任何方式格式不正确。
• 即使字符串具有正确的格式，您也可能遇到语言设置问题。

无论如何，也许这只是一个语言问题。试试这个

WHERE TO_DATE(BAL.PERIOD_NAME,'MON-RR', 'NLS_DATE_LANGUAGE = American') > DATE '2018-02-01'

如果您运行的是 Oracle 12.2 或更高版本，则可以使用函数VALIDATE_CONVERSION：

WHERE VALIDATE_CONVERSION(BAL.PERIOD_NAME, 'MON-RR', 'NLS_DATE_LANGUAGE = American') = 1

如果您还没有 Oracle 12.2，请编写自己的函数，例如：

CREATE OR REPLACE FUNCTION CONVERT_TO_DATE(str in VARCAHR2) RETURN DATE IS
BEGIN
RETURN TO_DATE(str, 'MON-RR', 'NLS_DATE_LANGUAGE = American');
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;  
END CONVERT_TO_DATE;