Have error ORA-01861 when I want to change it to "date column type"

Question

I have a table which calculates some measure base on the Date column type. also, that should be store date as Persian calendar but at the end when I using TO_DATE function for changing its type have an ORA-01861 error on 19,20,21 days of the May while there is such date is already exist and the second month of Persian calendar has 31 days. here is my query:

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

my oracle database version is Oracle 12.2.0.1.0

Answer 1

You convert a sting to a DATE, then to a string and back again to a DATE, that's useless.

Try this one:

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

or shorter:

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

Or use DATE literals and NLS session values:

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;