Convert Between Gregorian & Hijri Calendars Using Oracle to_date

There are two ways to convert from/to Hijri Calendar (also called Hijrah Calendar):

1) Use Oracle’s function to_date. This is discussed in this entry
2) Build your own conversion functions. You can read about this here

Oracle will convert from nls_calendar in to_date function to nls_calendar session parameter in your session. If both calendars are the same value then no conversion will occur.

Here are some examples:

— Show nls_calendar nls_session_parameters
SQL> select value from nls_session_parameters
2 where parameter = ‘NLS_CALENDAR’;

VALUE
———————
GREGORIAN

— Convert from Hijri calendar to Gregorian Calendar
SQL>
SQL> select col1 hijrah_date,
to_date(col1,’dd/mm/yyyy’, ‘nls_calendar=”Arabic Hijrah”’) to_gregorian
2 from hijrah_date;

HIJRAH_DATE TO_GREGOR
——————– ———
03/01/1429 12-JAN-08
03/02/1429 11-FEB-08

— Convert from Gregorian Calendar to Hijri
SQL> alter session set nls_calendar=’Arabic Hijrah’;

Session altered.

SQL> select value from nls_session_parameters
2 where parameter = ‘NLS_CALENDAR’;

VALUE
———————
Arabic Hijrah

SQL>
SQL> select col1 gregorian_date, to_date(col1,’dd/mm/yyyy’, ‘nls_calendar=”Gregorian”’) to_hijrah
2 from gregorian_date;

GREGORIAN_DATE TO_HIJRAH
——————– ———————
25-Jun-2008 20 جمادي الثانية 1429
17-Mar-2008 09 ربيع الأول 1429

— Convert from Greorgian Calendar to Hijri and show date in English

alter session set nls_calendar=’English Hijrah’;

select col1 hijrah_date, to_date(col1,’dd/mm/yyyy’, ‘nls_calendar=”GREGORIAN”’) to_gregorian
from gregorian_date;

Session altered.

HIJRAH_DATE TO_GREGORIAN
——————– ————————-
25-Jun-2008 20 Jamada El Thaniah 1429
17-Mar-2008 09 Rabi’ Awwal 1429

2 rows selected.

Hazem Ameen
Senior Oracle DBA

Advertisements

8 thoughts on “Convert Between Gregorian & Hijri Calendars Using Oracle to_date

  1. hi,i try somany times.but convertion factor is not working . i just change the nls_calender value only.this single valus is enough or any other value have to set.plz just forward to my mail .it is important to me.geswarao@gmail.com

  2. Hi,

    I’m not able to make this above piece working, its throwing the below error message. Could you please let me know if I need to anything in addition, other than the ones mentioned above..
    Error Msg:
    “ORA-12702: Invalid NLS Parameter String used in SQL Function”

    Thanks, Nandha

  3. Salam
    How can I display month name in arabic and hindi numbers when displaying date on oracle report
    Thanks anyway for this helpful blog

  4. Salam Alaikum friend,
    what is the best charactorset for both arabic and english for oracle 12.2 on windows 10
    i am having trouble finding the best one.
    thanks jazaka allahu khairan.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s