Convert Between Gregorian & Hijri Calendars Using Oracle’s PL/SQL

Working with Hijri calendar in Oracle can get frustrating especially with date conversion from/to Gregorian where results can be off by 2 days in some cases.

Here is a solution that does date conversion through lookup and totally eliminates the dependency on Oracle’s to_date function. If a converted date happens to be off, just update the lookup table with correct values.

1. Create Lookup Table

drop table date_conversion;

create table date_conversion (
seq number(10),
h_date varchar2(10),
g_date varchar2(10),
constraint date_conversion_pk primary key (seq) using index tablespace users
) pctfree 0;

create index date_conversion_h_ix on date_conversion (h_date) tablespace users;

create index date_conversion_g_ix on date_conversion (g_date) tablespace users;

2. Insert data in Lookup Table

You can insert as many years as you like, 10 years account for about 3,500 rows only.

Just as an example:

insert into date_conversion values (1, ‘1419/01/01’, ‘1998/04/28’);
insert into date_conversion values (2, ‘1419/01/02’, ‘1998/04/29’);
.
.
insert into date_conversion values (3715, ‘1429/06/24’, ‘2008/06/29’);

commit;

In real life, you would have to do this through a loop or a more efficient way.

3. Create Stored Procedures

These functions will be called to perform calendar conversion and date math.

CREATE OR REPLACE
FUNCTION hijri_add(in_h_date varchar2, in_days in number)
return varchar2 IS

out_date varchar2(10);

begin

select h_date into out_date
from date_conversion
where seq =
(select seq + in_days
from date_conversion
where h_date = in_h_date);

return out_date;

end;
/

CREATE OR REPLACE
FUNCTION to_gregorian(in_h_date varchar2)
return varchar2 IS
out_date varchar2(10);
begin
select g_date into out_date
from date_conversion
where h_date = in_h_date;
return out_date;
end;
/

CREATE OR REPLACE
FUNCTION to_hijri(in_g_date varchar2)
return varchar2 IS
out_date varchar2(10);
begin
select h_date into out_date
from date_conversion
where g_date = in_g_date;
return out_date;
end;
/

Examples

select to_hijri(to_char(sysdate, ‘YYYY/MM/DD’)) hijri_date from dual;

HIJRI_DATE
————-
1429/06/24

select to_gregorian (‘1429/06/24’) “Gregorian Date” from dual;

Gregorian Date
—————
2008/06/29

select hijri_add (‘1429/06/02’, 22) “New Date” from dual;

New Date
———–
1429/06/24

Performance Enhancement

This table is ideal to be placed in keep cache, also remember to cache the indexes too. Alternately, you can cache the entire table in PL/SQL table. This will consume some of PGA memory depends on the amount of data in the lookup table (PL/SQL table will be cached for every session not just 1 time).

Advertisements

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