how do i identify a particular date in oracle is a business day. Can i get the sample code?

sum_it_jec
sum_it_jec used Ask the Experts™
on
how do i identify a particular date in oracle is a business day. Can i get the sample code?

i have a date input and i wants to know whether this date is a business day.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
Check business_date  function in the below link which would give you a clear idea:

http://www.psoug.org/reference/date_func.html
Most Valuable Expert 2011
Top Expert 2012
Commented:
are you defining business day to be Monday-Friday?

case when to_char(your_date,'Dy') in ('Sat','Sun')
           then 'Not a business day'
           else 'Is a business day'
end
There is no specific function to do this in Oracle, and Bank Holidays are different in every country so you will need to use a combination of the to_char function and a table that holds Public Holiday dates.

The to_char(sysdate,'D') function will give you a number identifying the day of the week, where day 1 is Monday.  If this gives a value between 1 and 5 then it is a week day.  You will then need to check the date in your Public Holiday table and if it doesn't exist then it is a normal business day.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Commented:
Hi,

to_char(date,'D') will return a number indicating the weekday. However, for a given date, this number returned varies with NLS_TERRITORY.

For more information : http://www.adp-gmbh.ch/ora/sql/to_char.html

Regards
Commented:
There is nothing built-in to Oracle to support a "business day".  I presume you mean Monday to Friday (inclusive).  If that is all you want, then you can do that easily with straight SQL (see sdstuber's post above).  

However if you want public holidays you'll have to extend that.  The easiest approach would be to have a PUBLIC_HOLIDAYS table, but the downside is that you would have to maintain that table. AND do you want to cater for different countries? - which have different public holidays.

I've included a simple example below.  If you want to call the function from SQL, you'll have to change the return type to NUMBER, VARCHAR (as BOOLEAN type isn't supported in SQL).  


CREATE TABLE public_holidays
( dy DATE )
/
-- populate with data ...
 
CREATE OR REPLACE FUNCTION isBusinessDay(pDay DATE) RETURN BOOLEAN IS
   lisHoliday INTEGER;
BEGIN
   IF to_char(pDay, 'DY') IN ('SAT','SUN') THEN
      RETURN FALSE;
   ELSE
      -- check for public/bank-holidays
      SELECT COUNT(*)
      INTO   lisHoliday
      FROM   public_holidays
      WHERE  dy = trunc(pDay);
   
      RETURN lisHoliday <> 1;
   END IF;
END isBusinessDay;
/

Open in new window

Naveen KumarProduction Manager / Application Support Manager
Commented:
the term business day itself is ambiguous and has many meanings in many part of the world.

some places it just means a working day ( excluding weekends Saturday/Sunday and public holidays ) and in some places they refer to them as a working day ( excluding only Sunday and public holidays ) and so on...

ideally your application should have one table which can give the list of all public holidays for the company along with which we can then exclude the weekends as well and then determine if it a business day or not.

you need to use to_char(...) and to_date(..) functions speaking technically in your sql queries to determine that.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial