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

4 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

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s