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).
