当我想要将它更改为"date column type“时，出现错误ORA-01861

Question

我有一个表，它根据Date列类型计算一些度量。此外，这应该是存储日期作为波斯日历，但在结束时，当我使用TO_DATE函数更改其类型有一个ORA-01861错误的19,20,21天的五月，而有这样的日期已经存在，第二个月的波斯历有31天。下面是我的问题：

with a as
 (select to_char(to_date('20190518', 'YYYYMMDD'),
                 'YYYYMMDD',
                 'NLS_CALENDAR=PERSIAN') PERSIAN_DATE
    from DUAL
  UNION ALL
  select to_char(to_date('20190519', 'YYYYMMDD'),
                 'YYYYMMDD',
                 'NLS_CALENDAR=PERSIAN') PERSIAN_DATE
    from DUAL
  UNION ALL
  select to_char(to_date('20190520', 'YYYYMMDD'),
                 'YYYYMMDD',
                 'NLS_CALENDAR=PERSIAN') PERSIAN_DATE
    from DUAL
  UNION ALL
  select to_char(to_date('20190521', 'YYYYMMDD'),
                 'YYYYMMDD',
                 'NLS_CALENDAR=PERSIAN') PERSIAN_DATE
    from DUAL
  UNION ALL
  select to_char(to_date('20190522', 'YYYYMMDD'),
                 'YYYYMMDD',
                 'NLS_CALENDAR=PERSIAN') PERSIAN_DATE
    from DUAL)
SELECT TO_DATE(PERSIAN_DATE, 'YYYY/MM/DD') pers_date FROM A

我的oracle数据库版本是Oracle 12.2.0.1.0

Answer 1

您将一个字符串转换为日期，然后将其转换为字符串，然后再转换回日期，这是没有用的。

试试这个：

WITH a AS
 (SELECT TO_DATE('20190518', 'YYYYMMDD') PERSIAN_DATE
    FROM DUAL
  UNION ALL
  SELECT TO_DATE('20190519', 'YYYYMMDD') PERSIAN_DATE
    FROM DUAL
  UNION ALL
  SELECT TO_DATE('20190520', 'YYYYMMDD') PERSIAN_DATE
    FROM DUAL
  UNION ALL
  SELECT TO_DATE('20190521', 'YYYYMMDD') PERSIAN_DATE
    FROM DUAL
  UNION ALL
  SELECT TO_DATE('20190522', 'YYYYMMDD') PERSIAN_DATE
    FROM DUAL)
SELECT to_char(PERSIAN_DATE, 'YYYYMMDD', 'NLS_CALENDAR=PERSIAN') pers_date 
FROM A

或更短：

SELECT TO_CHAR(TO_DATE('20190517', 'YYYYMMDD')+LEVEL, 'YYYYMMDD', 'NLS_CALENDAR=PERSIAN') pers_date 
FROM dual
CONNECT BY LEVEL <= 5;

或者使用DATE文本和NLS会话值：

ALTER SESSION SET NLS_CALENDAR = 'PERSIAN';
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYYMMDD'; 

SELECT DATE '2019-05-17' + LEVEL pers_date 
FROM dual
CONNECT BY LEVEL <= 5;

Answer 2

当您仅在第18天执行select (您没有遇到问题)并添加原始值时，您将看到它将返回

with a as
     (select to_char(to_date('20190518', 'YYYYMMDD'),
                     'YYYYMMDD',
                     'NLS_CALENDAR=PERSIAN') PERSIAN_DATE
        from DUAL)
    SELECT PERSIAN_DATE, TO_DATE(PERSIAN_DATE, 'YYYY/MM/DD') pers_date FROM A;

Persian Date    pers_date
13980228        28/FEB/98  00:00:00  

因此，oracle正在尝试丢弃前两个字符，并尽可能从其余字符中选择最好的日期(我非常确定这不是正确的波斯日期)。

所以当你为19号运行这个时，遵循同样的逻辑(我们只能得到原始值，因为日期将会出错)

with a as
  (select to_char(to_date('20190519', 'YYYYMMDD'),
                 'YYYYMMDD',
                 'NLS_CALENDAR=PERSIAN') PERSIAN_DATE
    from DUAL)
SELECT PERSIAN_DATE pers_date FROM A;

Persian Date
13980229

按照与上面相同的逻辑，去掉前两个字符，您将得到1998年2月29日...这不是有效的日期。

正如其他人在注释中指出的那样，Oracle只存储它不关心日历的日期，您需要转换为适当的日历以进行显示。

有关oracle如何存储日期的详细说明，请参阅

How are dates stored in Oracle?