Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Create a Persian calender with Oracle PL/SQL, in this case it creates a table of all dates from 30.12.1899 to 31.12.2099 for the use in QlikView
CREATE TABLE "OCEAN"."T_DATE_PERSIAN"
(
"QV_DATE_NUM" NUMBER(6,0),
"GREGORIAN_DATE" DATE,
"PERSIAN_DATE" VARCHAR2(10 CHAR)
)
;
create or replace
PROCEDURE INSERT_T_DATE_PERSIAN IS
--DECLARE
type t_date is table of t_date_persian%rowtype;
a_date t_date;
v_date_start date;
v_date_end date;
v_days number;
BEGIN
delete from t_date_persian;
commit;
v_date_start := to_date('30.12.1899','DD.MM.YYYY');
v_date_end := to_date('31.12.2099','DD.MM.YYYY');
v_days := v_date_end - v_date_start + 1;
a_date := t_date();
a_date.extend(v_days + 1);
for i in 0..v_days loop
a_date(i + 1).qv_date_num := i;
a_date(i + 1).gregorian_date := v_date_start + i;
a_date(i + 1).persian_date := to_char(v_date_start + i,'yyyy/mm/dd','nls_calendar=persian');
end loop;
forall i in a_date.first .. a_date.last
insert into t_date_persian values a_date(i);
commit;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END INSERT_T_DATE_PERSIAN;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.