Hijrah Date in Oracle Database

Some Arabic countries use Hijrah calendar specially in Saudi Arabia.
So today I will post about Hijrah Date and how to convert between different calendars.

The main notes in this post
1-NLS Calendars in Oracle Database
2-Convert Georgian calendar to Hijrah calendar
3-Convert Hijrah Calendar to Georgian
4-Fix deviations between calendars
5-How to create lexical file for the deviation

1-NLS Calendars in Oracle Database

NLS_CALENDAR is parameter in oracle database which specify calendar system database uses

Different calendar systems supported  in oracle database

a-Arabic Hijrah

b-English Hijrah

c-Gregorian

d-Japanese Imperial

e-Persian

f-ROC Official (Republic of China)

g-Thai Buddha

You can change calendar system in database per session using below command

ALTER SESSION SET NLS_CALENDAR = 'calendar system';

Note you can alter NLS_CALENDAR at three levels

a-Session

b-Instance

c-Database

For example to change calendar system to arabic hijrah in your session

ALTER SESSION SET NLS_CALENDAR = 'arabic hijrah';

2-Convert Georgian calendar to Hijrah calendar

I will use TO_CHAR function to convert between different calendars
The general syntax of TO_CHAR when use it to convert between different calendars like below
TO_CHAR(DATE,'DATE_FORMAT','NLS_CALENDAR=''calendar system''');

For example to convert current database system calendar to Arabic Hijrah

SELECT TO_CHAR (SYSDATE, 'dd-mm-rrrr', 'nls_calendar=''arabic hijrah''')
  FROM DUAL;

3-Convert Hijrah Calendar to Georgian

Suppose that your current database system calendar is Gregorian and you have literal Hijrah date and you want to convert this literal date to Gregorian.

Suppose that I have '23-05-1433' Literal which is Hijrah date and I want to convert it to Gregorian, You can use below SQL
SELECT TO_CHAR (TO_DATE ('23-05-1433', 'dd-mm-rrrr'),
                'dd-mm-rrrr',
                'nls_calendar=''Gregorian''')
  FROM DUAL;

4-Fix deviations between calendars

The hijrah calendar is related by the moon complete revolution around the sun, which may be different from area to another.
Oracle support hijrah date regarding specific calculation.
When existing different between  hijrah date in database and the real date of hijrah calendar, oracle supports lexical file to fix this issue

5-How to create lexical file for the deviation
you will create text file with extension nlt and the below is the main sample of the lexical file

DEFINE calendar
         calendar_name              = "Arabic Hijrah"
       DEFINE calendar_deviation
           deviation_data           = {
                <"MAY-18-1931 ad">:1
                <"AUG-14-1931 ad">:1
                <"SEP-12-1931 ad">:11

            }
       ENDDEFINE calendar_deviation

ENDDEFINE calendar

The word in blue color are static in the sample and the words in red color are variable regarding your calendar

First you specify which calendar system you will enter difference for it
  calendar_name              = "Arabic Hijrah"
 
Second you enter deviation per every date has deviation, take care because it is cumulative which mean if you change old date it will change any newer date. 
<"MAY-18-1931 ad">:1 
<"AUG-14-1931 ad">:1 
<"SEP-12-1931 ad">:11

How you enter the deviation of dates :-
Number greater than 10 refer to subtract days eg:
:11 subtract one day
:12 subtract two days
:13 subtract three days etc..

Number less than or equal 10 refere to add days eg:
:1 add one day
:2 add two days
:3 add three days etc....

After creating your lexical file you should copy it in %ORACLE_HOME%/nls folder
and then run below nls calendar utility lxegen command from operating systems command to execute lexical file on database.
%ORACLE_HOME%\bin\lxegen %ORACLE_HOME%\nls\file_name.nlt

after executing this command it will generate new file in nls folder with extension  .nlb

Then shutdown and start-up the database and new modification in lexical file will take effect.

Thanks