Friday, May 23, 2008

Oracle Date Dimension

Friday, May 23, 2008 Posted by Andre Broers ,
In my opinion it is always handy to have a detailed date dimension in a database where you can always join against. This is handy to get the current month or all mondays that aren't holidays.

select datdate from dat where datyyyymm = (select datyyyymm from dat where datdate=trunc(sysdate));

select datdate from dat where datdayeng = 'Monday' and datholiday='Y' ;

in the underlying code the dat dimension is created.
create table dat
(
datkey number not null,
datdate date not null,
dattype varchar2(3) not null,
datyyyy number(4) not null,
datyyyymm number(6) not null,
datyyyymmdd number(8) not null,
datyyyyww number(8) not null,
datmm number(2) not null,
datww number(2) not null,
datdd number(2) not null,
datmmcum number not null,
datwwcum number not null,
datddcum number not null,
datmontheng varchar2(25) not null,
datmonthned varchar2(25) not null,
datdayofweek number(1) not null,
datdayeng varchar2(25) not null,
datdayned varchar2(25) not null,
datweekend varchar2(1) not null,
datholiday varchar2(1) not null,
constraint dat_pkey primary key (datkey)
)
/

drop sequence datkey_seq;

create sequence datkey_seq
start with 1
increment by 1
nomaxvalue
/

create or replace trigger datkey_trigger
before insert on dat
for each row
begin
select datkey_seq.nextval into :new.datkey from dual;
end;
/

create unique index datdate_idx1 on dat (datdate)
/

declare g_startdate date := '01-JAN-1950';
g_enddate date := '31-DEC-2100';
d date := g_startdate;
g_weekcum number := 0;
g_week number := 0;
begin
while d < g_enddate
loop
if (g_week <> to_char(d,'YYYYIW')) then
g_week := to_char(d,'YYYYIW');
g_weekcum := g_weekcum + 1;
end if;
insert into dat (
datdate
,dattype
,datyyyy
,datyyyymm
,datyyyymmdd
,datyyyyww
,datmm
,datww
,datdd
,datmmcum
,datwwcum
,datddcum
,datmontheng
,datmonthned
,datdayofweek
,datdayeng
,datdayned
,datweekend
,datholiday
) values (
d
,'DAT'
,to_char(d,'YYYY')
,to_char(d,'YYYYMM')
,to_char(d,'YYYYMMDD')
,to_char(d,'YYYYIW')
,to_char(d,'MM')
,to_char(d,'IW')
,to_char(d,'DD')
,trunc(months_between(d,g_startdate))
,g_weekcum
,d - to_date(g_startdate) + 1
,to_char(d,'FMMonth','NLS_DATE_LANGUAGE=American')
,to_char(d,'FMMonth','NLS_DATE_LANGUAGE=Dutch')
,to_char(d,'D') -- Monday first weekday
,to_char(d,'FMDay','NLS_DATE_LANGUAGE=American')
,to_char(d,'FMDay','NLS_DATE_LANGUAGE=Dutch')
,case when to_char(d,'FMDAY','NLS_DATE_LANGUAGE=American') = 'SATURDAY' then 'Y'
when to_char(d,'FMDAY','NLS_DATE_LANGUAGE=American') = 'SUNDAY' then 'Y'
else 'N'
end
,'N'
);
d := d + 1;
end loop;
end;
/

-- First day of week is monday
alter session set nls_territory = "the netherlands"
/
update dat set datdayofweek=to_char(datdate,'D')
/
-- Kerst
update dat set datholiday='Y' where datmm=12 and (datdd=25 or datdd=26)
/
-- Koninginnedag
update dat set datholiday='Y' where datmm=4 and ((datdd=30 and datdayofweek!=7) or (datdd=29 and datdayofweek=6))
/
-- 5 mei
update dat set datholiday='Y' where datmm=5 and mod(datyyyy,5)=0
/
update dat set datholiday='Y' where datdate=to_date('19500409','YYYYMMDD') or datdate-1=to_date('19500409','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19500409','YYYYMMDD') or datdate-1=to_date('19500409','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19510325','YYYYMMDD') or datdate-1=to_date('19510325','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19520413','YYYYMMDD') or datdate-1=to_date('19520413','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19530405','YYYYMMDD') or datdate-1=to_date('19530405','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19540418','YYYYMMDD') or datdate-1=to_date('19540418','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19550410','YYYYMMDD') or datdate-1=to_date('19550410','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19560401','YYYYMMDD') or datdate-1=to_date('19560401','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19570421','YYYYMMDD') or datdate-1=to_date('19570421','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19580406','YYYYMMDD') or datdate-1=to_date('19580406','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19590329','YYYYMMDD') or datdate-1=to_date('19590329','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19600417','YYYYMMDD') or datdate-1=to_date('19600417','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19610402','YYYYMMDD') or datdate-1=to_date('19610402','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19620422','YYYYMMDD') or datdate-1=to_date('19620422','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19630414','YYYYMMDD') or datdate-1=to_date('19630414','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19640329','YYYYMMDD') or datdate-1=to_date('19640329','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19650418','YYYYMMDD') or datdate-1=to_date('19650418','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19660410','YYYYMMDD') or datdate-1=to_date('19660410','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19670326','YYYYMMDD') or datdate-1=to_date('19670326','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19680414','YYYYMMDD') or datdate-1=to_date('19680414','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19690406','YYYYMMDD') or datdate-1=to_date('19690406','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19700329','YYYYMMDD') or datdate-1=to_date('19700329','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19710411','YYYYMMDD') or datdate-1=to_date('19710411','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19720402','YYYYMMDD') or datdate-1=to_date('19720402','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19730422','YYYYMMDD') or datdate-1=to_date('19730422','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19740414','YYYYMMDD') or datdate-1=to_date('19740414','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19750330','YYYYMMDD') or datdate-1=to_date('19750330','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19760418','YYYYMMDD') or datdate-1=to_date('19760418','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19770410','YYYYMMDD') or datdate-1=to_date('19770410','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19780326','YYYYMMDD') or datdate-1=to_date('19780326','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19790415','YYYYMMDD') or datdate-1=to_date('19790415','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19800406','YYYYMMDD') or datdate-1=to_date('19800406','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19810419','YYYYMMDD') or datdate-1=to_date('19810419','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19820411','YYYYMMDD') or datdate-1=to_date('19820411','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19830403','YYYYMMDD') or datdate-1=to_date('19830403','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19840422','YYYYMMDD') or datdate-1=to_date('19840422','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19850407','YYYYMMDD') or datdate-1=to_date('19850407','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19860330','YYYYMMDD') or datdate-1=to_date('19860330','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19870419','YYYYMMDD') or datdate-1=to_date('19870419','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19880403','YYYYMMDD') or datdate-1=to_date('19880403','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19890326','YYYYMMDD') or datdate-1=to_date('19890326','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19900415','YYYYMMDD') or datdate-1=to_date('19900415','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19910331','YYYYMMDD') or datdate-1=to_date('19910331','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19920419','YYYYMMDD') or datdate-1=to_date('19920419','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19930411','YYYYMMDD') or datdate-1=to_date('19930411','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19940403','YYYYMMDD') or datdate-1=to_date('19940403','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19950416','YYYYMMDD') or datdate-1=to_date('19950416','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19960407','YYYYMMDD') or datdate-1=to_date('19960407','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19970330','YYYYMMDD') or datdate-1=to_date('19970330','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19980412','YYYYMMDD') or datdate-1=to_date('19980412','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19990404','YYYYMMDD') or datdate-1=to_date('19990404','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20000423','YYYYMMDD') or datdate-1=to_date('20000423','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20010415','YYYYMMDD') or datdate-1=to_date('20010415','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20020331','YYYYMMDD') or datdate-1=to_date('20020331','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20030420','YYYYMMDD') or datdate-1=to_date('20030420','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20040411','YYYYMMDD') or datdate-1=to_date('20040411','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20050327','YYYYMMDD') or datdate-1=to_date('20050327','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20060416','YYYYMMDD') or datdate-1=to_date('20060416','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20070408','YYYYMMDD') or datdate-1=to_date('20070408','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20080323','YYYYMMDD') or datdate-1=to_date('20080323','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20090412','YYYYMMDD') or datdate-1=to_date('20090412','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20100404','YYYYMMDD') or datdate-1=to_date('20100404','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20110424','YYYYMMDD') or datdate-1=to_date('20110424','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20120408','YYYYMMDD') or datdate-1=to_date('20120408','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20130331','YYYYMMDD') or datdate-1=to_date('20130331','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20140420','YYYYMMDD') or datdate-1=to_date('20140420','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20150405','YYYYMMDD') or datdate-1=to_date('20150405','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20160327','YYYYMMDD') or datdate-1=to_date('20160327','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20170416','YYYYMMDD') or datdate-1=to_date('20170416','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20180401','YYYYMMDD') or datdate-1=to_date('20180401','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20190421','YYYYMMDD') or datdate-1=to_date('20190421','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20200412','YYYYMMDD') or datdate-1=to_date('20200412','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20210404','YYYYMMDD') or datdate-1=to_date('20210404','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20220417','YYYYMMDD') or datdate-1=to_date('20220417','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20230409','YYYYMMDD') or datdate-1=to_date('20230409','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20240331','YYYYMMDD') or datdate-1=to_date('20240331','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20250420','YYYYMMDD') or datdate-1=to_date('20250420','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20260405','YYYYMMDD') or datdate-1=to_date('20260405','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20270328','YYYYMMDD') or datdate-1=to_date('20270328','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20280416','YYYYMMDD') or datdate-1=to_date('20280416','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20290401','YYYYMMDD') or datdate-1=to_date('20290401','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20300421','YYYYMMDD') or datdate-1=to_date('20300421','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20310413','YYYYMMDD') or datdate-1=to_date('20310413','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20320328','YYYYMMDD') or datdate-1=to_date('20320328','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20330417','YYYYMMDD') or datdate-1=to_date('20330417','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20340409','YYYYMMDD') or datdate-1=to_date('20340409','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20350325','YYYYMMDD') or datdate-1=to_date('20350325','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20360413','YYYYMMDD') or datdate-1=to_date('20360413','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20370405','YYYYMMDD') or datdate-1=to_date('20370405','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20380425','YYYYMMDD') or datdate-1=to_date('20380425','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20390410','YYYYMMDD') or datdate-1=to_date('20390410','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20400401','YYYYMMDD') or datdate-1=to_date('20400401','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20410421','YYYYMMDD') or datdate-1=to_date('20410421','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20420406','YYYYMMDD') or datdate-1=to_date('20420406','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20430329','YYYYMMDD') or datdate-1=to_date('20430329','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20440417','YYYYMMDD') or datdate-1=to_date('20440417','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20450409','YYYYMMDD') or datdate-1=to_date('20450409','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20460325','YYYYMMDD') or datdate-1=to_date('20460325','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20470414','YYYYMMDD') or datdate-1=to_date('20470414','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20480405','YYYYMMDD') or datdate-1=to_date('20480405','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20490418','YYYYMMDD') or datdate-1=to_date('20490418','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20500410','YYYYMMDD') or datdate-1=to_date('20500410','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20510402','YYYYMMDD') or datdate-1=to_date('20510402','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20520421','YYYYMMDD') or datdate-1=to_date('20520421','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20530406','YYYYMMDD') or datdate-1=to_date('20530406','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20540329','YYYYMMDD') or datdate-1=to_date('20540329','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20550418','YYYYMMDD') or datdate-1=to_date('20550418','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20560402','YYYYMMDD') or datdate-1=to_date('20560402','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20570422','YYYYMMDD') or datdate-1=to_date('20570422','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20580414','YYYYMMDD') or datdate-1=to_date('20580414','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20590330','YYYYMMDD') or datdate-1=to_date('20590330','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20600418','YYYYMMDD') or datdate-1=to_date('20600418','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20610410','YYYYMMDD') or datdate-1=to_date('20610410','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20620326','YYYYMMDD') or datdate-1=to_date('20620326','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20630415','YYYYMMDD') or datdate-1=to_date('20630415','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20640406','YYYYMMDD') or datdate-1=to_date('20640406','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20650329','YYYYMMDD') or datdate-1=to_date('20650329','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20660411','YYYYMMDD') or datdate-1=to_date('20660411','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20670403','YYYYMMDD') or datdate-1=to_date('20670403','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20680422','YYYYMMDD') or datdate-1=to_date('20680422','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20690414','YYYYMMDD') or datdate-1=to_date('20690414','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20700330','YYYYMMDD') or datdate-1=to_date('20700330','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20710419','YYYYMMDD') or datdate-1=to_date('20710419','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20720410','YYYYMMDD') or datdate-1=to_date('20720410','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20730326','YYYYMMDD') or datdate-1=to_date('20730326','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20740415','YYYYMMDD') or datdate-1=to_date('20740415','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20750407','YYYYMMDD') or datdate-1=to_date('20750407','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20760419','YYYYMMDD') or datdate-1=to_date('20760419','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20770411','YYYYMMDD') or datdate-1=to_date('20770411','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20780403','YYYYMMDD') or datdate-1=to_date('20780403','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20790423','YYYYMMDD') or datdate-1=to_date('20790423','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20800407','YYYYMMDD') or datdate-1=to_date('20800407','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20810330','YYYYMMDD') or datdate-1=to_date('20810330','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20820419','YYYYMMDD') or datdate-1=to_date('20820419','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20830404','YYYYMMDD') or datdate-1=to_date('20830404','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20840326','YYYYMMDD') or datdate-1=to_date('20840326','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20850415','YYYYMMDD') or datdate-1=to_date('20850415','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20860331','YYYYMMDD') or datdate-1=to_date('20860331','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20870420','YYYYMMDD') or datdate-1=to_date('20870420','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20880411','YYYYMMDD') or datdate-1=to_date('20880411','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20890403','YYYYMMDD') or datdate-1=to_date('20890403','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20900416','YYYYMMDD') or datdate-1=to_date('20900416','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20910408','YYYYMMDD') or datdate-1=to_date('20910408','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20920330','YYYYMMDD') or datdate-1=to_date('20920330','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20930412','YYYYMMDD') or datdate-1=to_date('20930412','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20940404','YYYYMMDD') or datdate-1=to_date('20940404','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20950424','YYYYMMDD') or datdate-1=to_date('20950424','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20960415','YYYYMMDD') or datdate-1=to_date('20960415','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20970331','YYYYMMDD') or datdate-1=to_date('20970331','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20980420','YYYYMMDD') or datdate-1=to_date('20980420','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20990412','YYYYMMDD') or datdate-1=to_date('20990412','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('21000328','YYYYMMDD') or datdate-1=to_date('21000328','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19500518','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19510503','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19520522','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19530514','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19540527','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19550519','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19560510','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19570530','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19580515','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19590507','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19600526','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19610511','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19620531','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19630523','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19640507','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19650527','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19660519','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19670504','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19680523','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19690515','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19700507','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19710520','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19720511','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19730531','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19740523','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19750508','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19760527','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19770519','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19780504','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19790524','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19800515','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19810528','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19820520','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19830512','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19840531','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19850516','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19860508','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19870528','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19880512','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19890504','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19900524','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19910509','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19920528','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19930520','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19940512','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19950525','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19960516','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19970508','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19980521','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19990513','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20000601','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20010524','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20020509','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20030529','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20040520','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20050505','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20060525','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20070517','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20080501','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20090521','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20100513','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20110602','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20120517','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20130509','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20140529','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20150514','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20160505','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20170525','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20180510','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20190530','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20200521','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20210513','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20220526','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20230518','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20240509','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20250529','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20260514','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20270506','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20280525','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20290510','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20300530','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20310522','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20320506','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20330526','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20340518','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20350503','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20360522','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20370514','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20380603','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20390519','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20400510','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20410530','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20420515','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20430507','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20440526','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20450518','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20460503','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20470523','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20480514','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20490527','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20500519','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20510511','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20520530','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20530515','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20540507','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20550527','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20560511','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20570531','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20580523','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20590508','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20600527','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20610519','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20620504','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20630524','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20640515','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20650507','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20660520','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20670512','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20680531','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20690523','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20700508','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20710528','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20720519','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20730504','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20740524','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20750516','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20760528','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20770520','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20780512','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20790601','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20800516','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20810508','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20820528','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20830513','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20840504','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20850524','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20860509','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20870529','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20880520','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20890512','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20900525','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20910517','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20920508','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20930521','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20940513','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20950602','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20960524','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20970509','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20980529','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20990521','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('21000506','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19500528','YYYYMMDD') or datdate-1=to_date('19500528','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19510513','YYYYMMDD') or datdate-1=to_date('19510513','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19520601','YYYYMMDD') or datdate-1=to_date('19520601','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19530524','YYYYMMDD') or datdate-1=to_date('19530524','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19540606','YYYYMMDD') or datdate-1=to_date('19540606','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19550529','YYYYMMDD') or datdate-1=to_date('19550529','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19560520','YYYYMMDD') or datdate-1=to_date('19560520','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19570609','YYYYMMDD') or datdate-1=to_date('19570609','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19580525','YYYYMMDD') or datdate-1=to_date('19580525','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19590517','YYYYMMDD') or datdate-1=to_date('19590517','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19600605','YYYYMMDD') or datdate-1=to_date('19600605','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19610521','YYYYMMDD') or datdate-1=to_date('19610521','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19620610','YYYYMMDD') or datdate-1=to_date('19620610','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19630602','YYYYMMDD') or datdate-1=to_date('19630602','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19640517','YYYYMMDD') or datdate-1=to_date('19640517','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19650606','YYYYMMDD') or datdate-1=to_date('19650606','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19660529','YYYYMMDD') or datdate-1=to_date('19660529','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19670514','YYYYMMDD') or datdate-1=to_date('19670514','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19680602','YYYYMMDD') or datdate-1=to_date('19680602','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19690525','YYYYMMDD') or datdate-1=to_date('19690525','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19700517','YYYYMMDD') or datdate-1=to_date('19700517','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19710530','YYYYMMDD') or datdate-1=to_date('19710530','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19720521','YYYYMMDD') or datdate-1=to_date('19720521','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19730610','YYYYMMDD') or datdate-1=to_date('19730610','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19740602','YYYYMMDD') or datdate-1=to_date('19740602','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19750518','YYYYMMDD') or datdate-1=to_date('19750518','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19760606','YYYYMMDD') or datdate-1=to_date('19760606','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19770529','YYYYMMDD') or datdate-1=to_date('19770529','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19780514','YYYYMMDD') or datdate-1=to_date('19780514','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19790603','YYYYMMDD') or datdate-1=to_date('19790603','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19800525','YYYYMMDD') or datdate-1=to_date('19800525','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19810607','YYYYMMDD') or datdate-1=to_date('19810607','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19820530','YYYYMMDD') or datdate-1=to_date('19820530','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19830522','YYYYMMDD') or datdate-1=to_date('19830522','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19840610','YYYYMMDD') or datdate-1=to_date('19840610','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19850526','YYYYMMDD') or datdate-1=to_date('19850526','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19860518','YYYYMMDD') or datdate-1=to_date('19860518','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19870607','YYYYMMDD') or datdate-1=to_date('19870607','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19880522','YYYYMMDD') or datdate-1=to_date('19880522','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19890514','YYYYMMDD') or datdate-1=to_date('19890514','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19900603','YYYYMMDD') or datdate-1=to_date('19900603','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19910519','YYYYMMDD') or datdate-1=to_date('19910519','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19920607','YYYYMMDD') or datdate-1=to_date('19920607','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19930530','YYYYMMDD') or datdate-1=to_date('19930530','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19940522','YYYYMMDD') or datdate-1=to_date('19940522','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19950604','YYYYMMDD') or datdate-1=to_date('19950604','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19960526','YYYYMMDD') or datdate-1=to_date('19960526','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19970518','YYYYMMDD') or datdate-1=to_date('19970518','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19980531','YYYYMMDD') or datdate-1=to_date('19980531','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19990523','YYYYMMDD') or datdate-1=to_date('19990523','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20000611','YYYYMMDD') or datdate-1=to_date('20000611','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20010603','YYYYMMDD') or datdate-1=to_date('20010603','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20020519','YYYYMMDD') or datdate-1=to_date('20020519','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20030608','YYYYMMDD') or datdate-1=to_date('20030608','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20040530','YYYYMMDD') or datdate-1=to_date('20040530','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20050515','YYYYMMDD') or datdate-1=to_date('20050515','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20060604','YYYYMMDD') or datdate-1=to_date('20060604','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20070527','YYYYMMDD') or datdate-1=to_date('20070527','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20080511','YYYYMMDD') or datdate-1=to_date('20080511','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20090531','YYYYMMDD') or datdate-1=to_date('20090531','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20100523','YYYYMMDD') or datdate-1=to_date('20100523','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20110612','YYYYMMDD') or datdate-1=to_date('20110612','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20120527','YYYYMMDD') or datdate-1=to_date('20120527','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20130519','YYYYMMDD') or datdate-1=to_date('20130519','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20140608','YYYYMMDD') or datdate-1=to_date('20140608','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20150524','YYYYMMDD') or datdate-1=to_date('20150524','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20160515','YYYYMMDD') or datdate-1=to_date('20160515','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20170604','YYYYMMDD') or datdate-1=to_date('20170604','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20180520','YYYYMMDD') or datdate-1=to_date('20180520','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20190609','YYYYMMDD') or datdate-1=to_date('20190609','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20200531','YYYYMMDD') or datdate-1=to_date('20200531','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20210523','YYYYMMDD') or datdate-1=to_date('20210523','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20220605','YYYYMMDD') or datdate-1=to_date('20220605','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20230528','YYYYMMDD') or datdate-1=to_date('20230528','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20240519','YYYYMMDD') or datdate-1=to_date('20240519','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20250608','YYYYMMDD') or datdate-1=to_date('20250608','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20260524','YYYYMMDD') or datdate-1=to_date('20260524','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20270516','YYYYMMDD') or datdate-1=to_date('20270516','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20280604','YYYYMMDD') or datdate-1=to_date('20280604','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20290520','YYYYMMDD') or datdate-1=to_date('20290520','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20300609','YYYYMMDD') or datdate-1=to_date('20300609','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20310601','YYYYMMDD') or datdate-1=to_date('20310601','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20320516','YYYYMMDD') or datdate-1=to_date('20320516','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20330605','YYYYMMDD') or datdate-1=to_date('20330605','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20340528','YYYYMMDD') or datdate-1=to_date('20340528','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20350513','YYYYMMDD') or datdate-1=to_date('20350513','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20360601','YYYYMMDD') or datdate-1=to_date('20360601','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20370524','YYYYMMDD') or datdate-1=to_date('20370524','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20380613','YYYYMMDD') or datdate-1=to_date('20380613','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20390529','YYYYMMDD') or datdate-1=to_date('20390529','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20400520','YYYYMMDD') or datdate-1=to_date('20400520','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20410609','YYYYMMDD') or datdate-1=to_date('20410609','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20420525','YYYYMMDD') or datdate-1=to_date('20420525','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20430517','YYYYMMDD') or datdate-1=to_date('20430517','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20440605','YYYYMMDD') or datdate-1=to_date('20440605','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20450528','YYYYMMDD') or datdate-1=to_date('20450528','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20460513','YYYYMMDD') or datdate-1=to_date('20460513','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20470602','YYYYMMDD') or datdate-1=to_date('20470602','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20480524','YYYYMMDD') or datdate-1=to_date('20480524','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20490606','YYYYMMDD') or datdate-1=to_date('20490606','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20500529','YYYYMMDD') or datdate-1=to_date('20500529','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20510521','YYYYMMDD') or datdate-1=to_date('20510521','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20520609','YYYYMMDD') or datdate-1=to_date('20520609','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20530525','YYYYMMDD') or datdate-1=to_date('20530525','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20540517','YYYYMMDD') or datdate-1=to_date('20540517','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20550606','YYYYMMDD') or datdate-1=to_date('20550606','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20560521','YYYYMMDD') or datdate-1=to_date('20560521','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20570610','YYYYMMDD') or datdate-1=to_date('20570610','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20580602','YYYYMMDD') or datdate-1=to_date('20580602','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20590518','YYYYMMDD') or datdate-1=to_date('20590518','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20600606','YYYYMMDD') or datdate-1=to_date('20600606','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20610529','YYYYMMDD') or datdate-1=to_date('20610529','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20620514','YYYYMMDD') or datdate-1=to_date('20620514','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20630603','YYYYMMDD') or datdate-1=to_date('20630603','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20640525','YYYYMMDD') or datdate-1=to_date('20640525','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20650517','YYYYMMDD') or datdate-1=to_date('20650517','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20660530','YYYYMMDD') or datdate-1=to_date('20660530','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20670522','YYYYMMDD') or datdate-1=to_date('20670522','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20680610','YYYYMMDD') or datdate-1=to_date('20680610','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20690602','YYYYMMDD') or datdate-1=to_date('20690602','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20700518','YYYYMMDD') or datdate-1=to_date('20700518','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20710607','YYYYMMDD') or datdate-1=to_date('20710607','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20720529','YYYYMMDD') or datdate-1=to_date('20720529','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20730514','YYYYMMDD') or datdate-1=to_date('20730514','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20740603','YYYYMMDD') or datdate-1=to_date('20740603','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20750526','YYYYMMDD') or datdate-1=to_date('20750526','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20760607','YYYYMMDD') or datdate-1=to_date('20760607','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20770530','YYYYMMDD') or datdate-1=to_date('20770530','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20780522','YYYYMMDD') or datdate-1=to_date('20780522','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20790611','YYYYMMDD') or datdate-1=to_date('20790611','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20800526','YYYYMMDD') or datdate-1=to_date('20800526','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20810518','YYYYMMDD') or datdate-1=to_date('20810518','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20820607','YYYYMMDD') or datdate-1=to_date('20820607','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20830523','YYYYMMDD') or datdate-1=to_date('20830523','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20840514','YYYYMMDD') or datdate-1=to_date('20840514','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20850603','YYYYMMDD') or datdate-1=to_date('20850603','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20860519','YYYYMMDD') or datdate-1=to_date('20860519','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20870608','YYYYMMDD') or datdate-1=to_date('20870608','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20880530','YYYYMMDD') or datdate-1=to_date('20880530','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20890522','YYYYMMDD') or datdate-1=to_date('20890522','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20900604','YYYYMMDD') or datdate-1=to_date('20900604','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20910527','YYYYMMDD') or datdate-1=to_date('20910527','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20920518','YYYYMMDD') or datdate-1=to_date('20920518','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20930531','YYYYMMDD') or datdate-1=to_date('20930531','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20940523','YYYYMMDD') or datdate-1=to_date('20940523','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20950612','YYYYMMDD') or datdate-1=to_date('20950612','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20960603','YYYYMMDD') or datdate-1=to_date('20960603','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20970519','YYYYMMDD') or datdate-1=to_date('20970519','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20980608','YYYYMMDD') or datdate-1=to_date('20980608','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20990531','YYYYMMDD') or datdate-1=to_date('20990531','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('21000516','YYYYMMDD') or datdate-1=to_date('21000516','YYYYMMDD');

commit
/

I think it is handy to create this dimension in it's own schema:

create user dat identified by dat;
grant connect,resource to dat;
grant create public synonym to dat;
connect dat/dat
run the above script
grant select on dat to public;
create public synonym dat for dat;

Now everyone can use the dat dimension.