Solved

Calendar function in Oracle

Posted on 2001-08-29
5
996 Views
Last Modified: 2007-12-19
Hi,

I am currently using oracle 8.0.5 to create web application for the production users. The problem that I face here is the application do not have working calendar which consists of our company holidays. Do Oracle itself provide any buid in calendar function that can be modified accordingly to different working calendar? Your advice is highly appreciated.

Thanks,
Chewlf
0
Comment
Question by:chewlf
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 

Expert Comment

by:raghav3
ID: 6439181
Hi chewlf,
        The calender can be designed using the Forms and to be used/modified according to the company requirement.
Your application can use the designed calender which would be similar to any calender available.
        What front end u are using pls let me know so that i can suggest the suitable solution.
        U can also use the Date() function and Dynamic Sql to deal with the problem



Thanks,
Raghav
0
 
LVL 6

Accepted Solution

by:
Jankovsky earned 50 total points
ID: 6440363
Hi,
Oracle doesn't have working calendar functions.
You should ctrate table for working days specification and create own stored procedures.
We usualy use:
Create table WD(YEAR Varchar2(4) PRIMATY KEY ,MASK Varchar2(366));
Each character in MASK can be 'Y' for working day and 'N' for holiday.

function is_holiday (aDt Date) return Boolean is
  vYEAR Varchar2(4):=To_char(aDT,'YYYY');
  vDay  Integer:=To_number(To_char(aDT,'DDD'));
  vMask Varchar2(366);
Begin
  Select Mask into vMask from WD where YEAR=aYEAR;
  return(Sunstr(vMask,vDay,1)='N');
End is_holiday;

function Workingdays (aDt1 Date,aDt2 Date) return Number is
  vYEAR1 Varchar2(4):=To_char(aDT1,'YYYY');
  vDay1  Integer:=To_number(To_char(aDT1,'DDD'));
  vYEAR2 Varchar2(4):=To_char(aDT2,'YYYY');
  vDay2  Integer:=To_number(To_char(aDT2,'DDD'));
  vd1    Integer;
  vd2    Integer;
  vS     Varchar2(366);
  vCnt   Number:=0;
Begin
  for r1 in (Select YAER,MASK from WD where YEAR between
             aYEAR1 and aYEAR2 order by YEAR) LOOP
    if r1.YEAR=vYEAR1 then
      d1:=vDay1;
    else  
      d1:=1;
    end if;
    if r1.YEAR=vYEAR2 then
      d2:=vDay2;
    else  
      d2:=366;
    end if;
    vS:=Replace(Substr(r1.MASK,d1,d2),'N');
    vCnt:=vCnt+Length(vS);
  end LOOP;
  return vCnt;
End is_holiday;

There is quiet a lot of possibilities how do it.

Regards

0
 

Author Comment

by:chewlf
ID: 6449461
I am using HTML to display the application in IE.

You have a good suggestion, Jankovsky. I will try it out. Thanks.

Anybody know how to use owa_util.calendarprint? I think this maybe the another alternative to do it. I am trying to find any Oracle build in function that do not need to create any table form.

thanks and regards,
chewlf
0
 
LVL 6

Expert Comment

by:Mindphaser
ID: 7042902
Please update and finalize this old, open question. Please:

1) Award points ... if you need Moderator assistance to split points, comment here with details please or advise us in Community Support with a zero point question and this question link.
2) Ask us to delete it if it has no value to you or others
3) Ask for a refund so that we can move it to our PAQ at zero points if it did not help you but may help others.

EXPERT INPUT WITH CLOSING RECOMMENDATIONS IS APPRECIATED IF ASKER DOES NOT RESPOND.

Thanks,

** Mindphaser - Community Support Moderator **

P.S.  Click your Member Profile, choose View Question History to go through all your open and locked questions to update them.
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7058277
Recommended disposition:

    Accept Jankovsky's comment(s) as an answer.

DanRollins -- EE database cleanup volunteer
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question