The TO_DATE function in Oracle SQL return ORA-01839 error when converting string ('1446-2-29') with Arabic Hijrah Calender to Date datatype

When Trying to convert Date ('1446/2/29') with Arabic Hijrah Calendar which corresponding to ('2024/9/4') with Gregorian calendar it return ORA-01839: date not valid for month specified error. while the greater date ('1446/2/30') with Arabic Hijrah Calendar which corresponding to ('2024/9/5') with Gregorian calendar of it working good. The following three Queries explain the scienario, where 28,29,30 /2/1446 at Arabic Hijrah Calendar corresponding 3,4,5 /9/2024 at Gregorian Calendar.

SELECT TO_DATE(TO_CHAR(TO_DATE('2024/9/3', 'YYYY/MM/DD'), 'YYYY/MM/DD', 'NLS_CALENDAR=''Arabic Hijrah'''), 'YYYY/MM/DD') as HijriDate from dual;

SELECT TO_DATE(TO_CHAR(TO_DATE('2024/9/4', 'YYYY/MM/DD'), 'YYYY/MM/DD', 'NLS_CALENDAR=''Arabic Hijrah'''), 'YYYY/MM/DD') as HijriDate from dual;

SELECT TO_DATE(TO_CHAR(TO_DATE('2024/9/5', 'YYYY/MM/DD'), 'YYYY/MM/DD', 'NLS_CALENDAR=''Arabic Hijrah'''), 'YYYY/MM/DD') as HijriDate from dual;

also same queries with Arabic Hijrah Calendar date

SELECT TO_DATE(TO_CHAR(TO_DATE('1446/2/28', 'YYYY/MM/DD', 'NLS_CALENDAR=''Arabic Hijrah'''), 'YYYY/MM/DD', 'NLS_CALENDAR=''Arabic Hijrah'''), 'YYYY/MM/DD') as HijriDate from dual

SELECT TO_DATE(TO_CHAR(TO_DATE('1446/2/29', 'YYYY/MM/DD', 'NLS_CALENDAR=''Arabic Hijrah'''), 'YYYY/MM/DD', 'NLS_CALENDAR=''Arabic Hijrah'''), 'YYYY/MM/DD') as HijriDate from dual

SELECT TO_DATE(TO_CHAR(TO_DATE('1446/2/30', 'YYYY/MM/DD', 'NLS_CALENDAR=''Arabic Hijrah'''), 'YYYY/MM/DD', 'NLS_CALENDAR=''Arabic Hijrah'''), 'YYYY/MM/DD') as HijriDate from dual

corresponding results

28/02/1446

ORA-01839: date not valid for month specified

30/02/1446

Why is that error raised with (29/2/1446) while not raised with (30/2/1446)?

TO_DATE(string, format_model, nls_settings) converts the string to a DATE and assumes both the input and output are using the Gregorian calendar.

If your input string is in a different calendar then you need to specify the calendar in the nls_settings parameter. The DATE output will always be in the Gregorian calendar.

When you convert the dates to strings using the Hijrah calendar you get the values 1446-02-28, 1446-02-29 and 1446-03-01 and when you convert then back to dates using TO_DATE without specifying a calendar then the Gregorian calendar is used and the Gregorian year 1446 was not a leap year so 1446-02-29 is invalid.

• If you want display the values in the Hijrah calendar, then leave them as a string.
• If you want the values as a DATE then they will be stored in the Gregorian calendar and you need to specify the calendar as the third argument to TO_DATE to convert from a different calendar (Hijrah) to a Gregorian DATE.

Like this:

WITH dates (dt) AS (
  SELECT DATE '2024-09-03' FROM DUAL UNION ALL
  SELECT DATE '2024-09-04' FROM DUAL UNION ALL
  SELECT DATE '2024-09-05' FROM DUAL UNION ALL
  SELECT TO_DATE('1446/2/28', 'YYYY/MM/DD', 'NLS_CALENDAR=''Arabic Hijrah''') FROM DUAL UNION ALL
  SELECT TO_DATE('1446/2/29', 'YYYY/MM/DD', 'NLS_CALENDAR=''Arabic Hijrah''') FROM DUAL UNION ALL
  SELECT TO_DATE('1446/2/30', 'YYYY/MM/DD', 'NLS_CALENDAR=''Arabic Hijrah''') FROM DUAL
)
SELECT dt,
       TO_CHAR(dt, 'YYYY/MM/DD', 'NLS_CALENDAR=''Arabic Hijrah''') AS HijriStr,
       TO_DATE(
         TO_CHAR(dt, 'YYYY/MM/DD', 'NLS_CALENDAR=''Arabic Hijrah''')
         DEFAULT NULL ON CONVERSION ERROR,
         'YYYY/MM/DD'
       ) AS NoCalendar,
       TO_DATE(
         TO_CHAR(dt, 'YYYY/MM/DD', 'NLS_CALENDAR=''Arabic Hijrah'''),
         'YYYY/MM/DD',
         'NLS_CALENDAR=''Arabic Hijrah'''
       ) as WithCalendar
FROM   dates;

Which outputs:

fiddle