CODEWITHSUNDEEP
oracle-databaseoracle12c
GhostMan
GhostMan

Reputation: 63

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

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

Upvotes: 0

Views: 234

Answers (2)

Shaun Peterson
Shaun Peterson

Reputation: 1790

When you execute your select just for the 18th (that you were not having an issue with) and adding the raw value you will see it is returning 

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

So oracle is trying discarding the first two characters and making the best date it can out of the rest (pretty sure this is not the correct Persian date).

So following the same logic when you run this for 19th (we can only get the raw value as to date will error)

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

Following the same logic as above discard the first two characters and you get 29 Feb 1998... this is not a valid date.

As others have stated in the comments Oracle just stores the date it doesn't care about the calendar, you need to convert to the appropriate calendar for display.

For a good explanation of how oracle stores dates see

How are dates stored in Oracle?

Upvotes: 0

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59455

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;

Upvotes: 1

Related Questions