How can I convert the date from Hijri to Gregorian? PL(sql) ..... I want to enter the Hijri date and the date of my birth

Question

select to_date(to_char(sysdate,'dd/mm/yyyy','nls_calendar=''arabic hijrah''') ,'dd/mm/yyyy')
from dual ;

select to_date(to_char(to_date('11/07/1443','dd/mm/yyyy','nls_calendar=''arabic hijrah'''),
                 'dd/mm/yyyy' ,'nls_calendar=''gregorian''') ,'dd/mm/yyyy')  
from dual ;   

Answer 1

In Oracle, a DATE is a binary data type consisting of 7 bytes representing each of

1. century;
2. year-of-century;
3. month;
4. day;
5. hour;
6. minute; and
7. second

It ALWAYS has those components and it is NEVER stored in any particular format but it is stored in the Gregorian calendar.

---

Converting Hijri to Gregorian Calendar

I want to enter the Hijri date

If you want to convert from a formatted string to a DATE data-type then use the TO_DATE function and the appropriate NLS_CALENDAR settings as you did in the question

SELECT TO_DATE(
         '1443-07-22 09:25:30',
         'YYYY-MM-DD HH24:MI:SS',
         'NLS_CALENDAR=''arabic hijrah'''
       ) AS now
FROM   DUAL;

Which outputs a DATE data-type (with the NLS_DATE_FORMAT set to YYYY-MM-DD HH24:MI:SS):

NOW
2022-02-24 09:25:30

If you want to DISPLAY the output as a string (and not as a DATE data-type) and explicitly set the format and specify the calendar then use TO_CHAR to do the formatting and conversion:

SELECT TO_CHAR(
         TO_DATE(
           '1443-07-22 09:25:30',
           'YYYY-MM-DD HH24:MI:SS',
           'NLS_CALENDAR=''arabic hijrah'''
         ),
         'YYYY-MM-DD HH24:MI:SS',
         'NLS_CALENDAR=''gregorian'''
       ) AS hijrah_to_gregorian
FROM   DUAL;

Which outputs:

HIJRAH_TO_GREGORIAN
2022-02-24 09:25:30

---

The Queries in the Question

If you want them as dates in the Gregorian calendar (which is the default) then the queries in the question can be simplified to:

SELECT sysdate FROM DUAL;

and:

SELECT TO_DATE(
         '11/07/1443',
         'dd/mm/yyyy',
         'nls_calendar=''arabic hijrah'''
       ) 
FROM   DUAL;

If you want the queries in the question to output a string formatted to use the Hijrah calendar then:

SELECT TO_CHAR(
         sysdate,
         'YYYY-MM-DD HH24:MI:SS',
         'NLS_CALENDAR=''arabic hijrah'''
       ) AS now
FROM   DUAL;

and

SELECT '11/07/1443' FROM DUAL;

---

Changing the session parameters

If you want to change the calendar in your current session (note: this will not change the calendar for any other user or any of your subsequent sessions), you can use:

ALTER SESSION SET NLS_CALENDAR = 'Arabic Hijrah';
ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS';

If you are using a client application that formats dates based on the NLS session parameters, then:

SELECT sysdate FROM DUAL;

Outputs:

SYSDATE
22/07/1443 09:46:54

and

SELECT DATE '2022-02-13' FROM DUAL;

(Note: date literals are still input using the Gregorian calendar and not the current session calendar.)

Outputs:

DATE'2022-02-13'
11/07/1443 00:00:00

db<>fiddle here