By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
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;
| DT | HIJRISTR | NOCALENDAR | WITHCALENDAR |
|---|---|---|---|
| 2024-09-03 00:00:00 | 1446/02/28 | 1446-02-28 00:00:00 | 2024-09-03 00:00:00 |
| 2024-09-04 00:00:00 | 1446/02/29 | null | 2024-09-04 00:00:00 |
| 2024-09-05 00:00:00 | 1446/03/01 | 1446-03-01 00:00:00 | 2024-09-05 00:00:00 |
| 2024-09-03 00:00:00 | 1446/02/28 | 1446-02-28 00:00:00 | 2024-09-03 00:00:00 |
| 2024-09-04 00:00:00 | 1446/02/29 | null | 2024-09-04 00:00:00 |
| 2024-09-05 00:00:00 | 1446/03/01 | 1446-03-01 00:00:00 | 2024-09-05 00:00:00 |