Q
Problem solve Get help with specific problems with your technologies, process and projects.

# Testing for holidays, part 2

I saw your previous solution for counting the number of work days. How can I modify it to not count dates in my...

holiday table?

```CREATE TABLE  holiday
(
id         NUMBER (10)    PRIMARY KEY,
start_dt   DATE           NOT NULL,
end_dt     DATE           NOT NULL,
cmnt       VARCHAR2 (100)
);
```

I've attached some sample data for the table.

The following is continued from part 1.

### Code for holiday_num Function

When comparing dates in Oracle, always remember that they contain a time component. When you ask "Is SYSDATE + 21 a holiday?", you may think you're asking "Is October 13 a holiday?", but actually you're asking "Is October 13, 1:08 p.m., a holiday?". Is the difference significant? It is if you inserted '13-Oct-2003' as the end of the holiday, because Oracle interprets that as being 12 midnight at the beginning of October 13, which is before 1:08 in the afternoon of the same day. Use the TRUNC function if you want to ignore the time of day.

```--  holiday_num returns a positive number if in_dt is
--  a holiday according to the holiday table (that is,
--  if in_dt falls between start_dt and end_dt (inclusive)
--  for some row in holiday.
--    The number returned is the id for that row (or for
--  an arbitrary one of the rows, if there are several.)
--    If in_dt is not a holiday, 0 is returned.  (Therefore,
--  don't use 0 as an id.)

CREATE OR REPLACE FUNCTION holiday_num
(
in_dt  DATE
)
RETURN  NUMBER
IS
first_id  holiday.id%TYPE;
BEGIN
SELECT  id
INTO   first_id
FROM   holiday
WHERE  TRUNC (in_dt) BETWEEN TRUNC (start_dt)
AND (end_dt)
AND  ROWNUM = 1;

RETURN  first_id;    -- It is a holiday
EXCEPTION
WHEN  NO_DATA_FOUND
THEN  RETURN 0;  -- Not a holiday
WHEN  OTHERS
THEN  RETURN NULL;
END  holiday_num;
```

### Sample Data

```INSERT INTO  holiday
(
id,
start_dt,
end_dt,
cmnt
)
VALUES
(
holiday_id_seq.NEXTVAL,
'13-Oct-2003',
'13-Oct-2003',
'Columbus Day'
);

INSERT INTO  holiday
(
id,
start_dt,
end_dt,
cmnt
)
VALUES
(
holiday_id_seq.NEXTVAL,
'27-Nov-2003',
'28-Nov-2003',
'Thanksgiving (Thursday); business closed Friday, too'
);
```

This was last published in September 2003

## Content

Find more PRO+ content and other member only offers, here.

#### Have a question for an expert?

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

#### Start the conversation

Send me notifications when other members comment.

## SearchDataManagement

• ### Kubernetes gains momentum in big data implementation process

Big data vendors and users are looking to Kubernetes-managed containers to help accelerate system and application deployments and...

• ### IT, others focus efforts as GDPR implementation date looms

The EU's General Data Protection Regulation is meant to bring better data privacy to bear in the age of big data. How GDPR ...

• ### Streaming tool from StreamSets eyes data in motion for GDPR

StreamSets software for inspecting big data brings governance to data in motion. Such capabilities may find more use as the ...

• ### Cambridge Analytica-Facebook case shows need for ethical data mining

Ethical data collection practices are becoming even more important, as cases like Cambridge Analytica's misuse of Facebook data ...

• ### Rethinking analytics processes spurs enterprise innovation

By taking a fresh look at the makeup of their analytics organizations, enterprises can innovate their business models and take ...

• ### Diversified data sets for analytics deliver top results

Analytics teams should focus on data diversity to ensure that their projects deliver the most meaningful insights -- but they ...

## SearchSAP

• ### Software license management tools can help with SAP indirect usage

Companies can use third-party license management tools to audit their SAP environment to address indirect access issues, but the ...

• ### New leadership and redesign may spark SAP Community revival

SAP Community Network was once the best forum for SAP developers to share ideas, but user activity declined significantly. Can ...

• ### SAP Ariba Live focuses on procurement for purpose

SAP Ariba Live 2018 focused on the idea that businesses can use procurement technology to do good in the world; for example, by ...

## SearchSQLServer

• ### ObjectRocket launches Azure MongoDB service

Count ObjectRocket among those pursuing Azure MongoDB deployments. This open source NoSQL database continues to find traction on ...

SQL Operations Studio simplifies routine administration of SQL Server and Azure SQL databases, making database development and ...

• ### Meltdown and Spectre fixes eyed for SQL Server performance issues

Microsoft has responded to the Spectre and Meltdown chip vulnerabilities with patches and other fixes. But IT teams need to sort ...

## TheServerSide.com

• ### IBM fiddles with Apple on new AI services

IBM and Apple extended their existing enterprise mobility partnership with tools to help developers build Watson-based AI ...

• ### A side-by-side comparison of MongoDB and Cassandra databases

How do you choose one NoSQL database over another? Expert Christopher Tozzi explains the difference between MongoDB and Cassandra.

• ### Rust and Python top list of most popular development languages

A new Stack Overflow survey of over 100,000 developers indicated Java and JavaScript are not as popular as they had been before. ...

## SearchDataCenter

• ### IBM Cloud services to secure mainframes out to the edge

Big Blue will introduce IBM Cloud services that use blockchain, containers and its z14 mainframes to deliver improved security ...

• ### Four disadvantages of hyper-converged infrastructure systems

Problems with scalability and unexpected licensing costs can create problems for organizations that deploy hyper-converged ...

• ### IBM Power9 servers seek market inroads to AI, cloud

IBM follows up its first Power9 server with a raft of systems designed to appeal to a wider array of markets -- most notably, AI ...

## SearchContentManagement

• ### Box CEO weighs in on Dropbox IPO, AI in content management

In a wide-ranging conversation, Box CEO talks about Dropbox's public offering, AI's next application for content, Salesforce ...

• ### Scrivito unveils serverless CMS product

By building the CMS with ReactJS, Scrivito gained attraction with development community, according to an analyst.

• ### Content personalization tools sharpen focus on customers

Content personalization isn't new; Amazon weaponized it, and Jeff Bezos is the world's richest man. New tools are putting it ...

## SearchHRSoftware

• ### Wurk delivers tools to help cannabis industry manage business

A growing startup, Wurk, markets an HR software platform for the cannabis industry that handles the complex payroll, compliance ...

• ### Oracle HCM Cloud gets 'big' upgrade

Oracle is bringing consumerlike approaches to its HCM cloud applications. That includes using virtual assistants and intelligent ...

• ### Why the talent management revolution pivots on AI for HR

In this Q&A, HR analyst Jeanne Meister explains how AI will transform the way companies deal with talent and how HR departments ...

Close