ravgopal

 Profile Answers by ravgopal 

• Oct 10th, 2007

 

Did you find the solution?  if not then, here it is..try this

a) Create the sample table holiday :
 create table holiday (h_dt date, h_desc varchar2(100));

b) let us feed some records;

Insert into holiday values ('1-jan-2007','New Year');
Insert into holiday values ('8-oct-2007','Columbus day');

c) Querying holiday table, you will find :

H_DT                 H_DESC
---------           -------------
01-JAN-07      New Year

08-OCT-07     Columbus day

d) PL/SQL code to get the date
------------------------------------------------------------------
Declare

in_dt date := '&Dt'; --input date goes here
v_dt date;
v_chk number;

begin

<<start_here>>

select count(1) into v_chk from holiday where h_dt=in_dt;

if v_chk > 0 then
        in_dt := in_dt-1;
        goto start_here; --Loop to check consecutive holidays
end if;

select decode(to_char(in_dt,'DY'),'SUN',in_dt-2,'SAT',in_dt-1,in_dt)
into v_dt from dual;

dbms_output.put_line(v_dt);

end;
/

------------------------------------------------------------------

e) Output :

SQL> @lastbusiness
Enter value for dt: 8-Oct-2007
Result : 05-OCT-07

SQL> @lastbusiness
Enter value for dt: 2-Jan-2007
Result : 02-JAN-07

SQL> @lastbusiness
Enter value for dt: 1-Jan-2007
Result : 29-DEC-06

ravgopal

 Profile Answers by ravgopal 

• Oct 10th, 2007

 

if you need to get the previous business date, just change the in_dt variable in declaration to

input_dt date := '&Dt';
in_dt date := input_dt - 1;