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

# Calculating approval date, part 1: Counting days

How can I write a function to calculate the approval date which is n days (variable) from a given date, excluding Thursdays and Fridays?

So the Approvals Office promises results, sometimes in 0 days, sometimes in 1 day, and so on, but Office is closed on Thursdays and Fridays. The simplest solution I can imagine simulates how you might solve this manually, using a calendar and counting down:

``` -- approval_dt returns the date that is in_day_cnt "work" days -- after in_event_dt. -- A "work" day is any day except Thursday or Friday. -- If in_day_cnt <= 0, in_event_dt (or the next work day, -- if in_event_dt is not one) will be returned. CREATE OR REPLACE FUNCTION approval_dt ( in_event_dt IN DATE, -- Starting date in_day_cnt IN NUMBER -- Number of "work" days ) RETURN DATE IS BEGIN IF TO_CHAR (in_event_dt, 'Dy') IN ('Thu', 'Fri') THEN -- On off-days, skip ahead with same count RETURN approval_dt ( in_event_dt + 1, in_day_cnt ); ELSIF in_day_cnt <= 0 THEN -- this is the day wanted RETURN in_event_dt; ELSE -- skip ahead and count down RETURN approval_dt ( in_event_dt + 1, in_day_cnt - 1 ); END IF; END;
```

This is a great solution in several ways:

• It works
• You can easily prove that it works
• It's easy to modify (say the off-days change to Tuesdays and Fridays, or you have to exclude holidays, or the rules change during the summer months)

In fact, the only thing I can see wrong with this solution is that it's slow, especially for large values of in_day_cnt. If this is too slow for you, there is another solution.

Click for part 2 of Frank's response, A faster day counter.

This was last published in September 2003

#### 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

• ### Cloudera and Hortonworks combo to push CDP, machine learning

Two wunderkinds of Hadoop have formalized their merger. Cloudera and Hortonworks say they will place special focus on AI as they ...

• ### Why organizations need a solid data governance strategy

Data management has evolved from centralized data accessible by only the IT department to a flood of data stored in data ...

• ### IBM Weather Channel location data use spurs privacy concerns

IBM CEO Ginni Rometty pushed real-time weather data mining for travel and other uses, even as the Los Angeles city attorney is ...

• ### AI, graph databases among top BI and analytics trends

Leading trends in BI and analytics include machine learning, multi-cloud and graph databases. IT expert William McKnight details ...

• ### MicroStrategy 2019 platform touts 'zero-click' analytics

MicroStrategy's latest BI platform supports a 'zero-click' analytics future with help from new voice and vision capabilities. The...

• ### Analytics trends we'll see in 2019

BI and analytics software looks to evolve further in 2019, as tools will keep getting easier to use and more AI-powered ...

## SearchSAP

• ### UX analysis is key to S/4HANA migration at AES

User experience software from Knoa helped an energy company choose what transactions to migrate from SAP ECC 6 on an Oracle ...

• ### SAP Leonardo makes progress with new branding strategy

Leonardo initially suffered from high expectations, but SAP is unearthing opportunities in new industries to improve its branding...

• ### SAP intelligent enterprise, C/4HANA are trends to watch in 2019

Industry experts sound off on what SAP trends customers should watch in 2019. They also don't mince words about where SAP should ...

## SearchSQLServer

• ### SQL Server 2019 improves Linux, container support

The SQL Server 2019 release includes new big data integration features, a collection of database engine enhancements and improved...

• ### Faster R&D pace complicates SQL Server upgrades for users

Microsoft is updating SQL Server and Azure SQL Database more quickly than it did in the past -- and keeping up with the new ...

• ### Microsoft data platform gets new cloud, on-prem features

Microsoft released a second preview of SQL Server 2019 and said users can now run applications in either on-premises SQL Server ...

## TheServerSide.com

• ### Succeed with the test-first Java certification prep approach

Java certification exam prep is an important step for anyone interested in the test. Consider a test-first approach that might ...

• ### Want a private GitHub repository? It comes with a catch

Want a private GitHub repo? You can now create one with a basic account, but the feature set is somewhat limited.

• ### Java developer tutorials a popular destination for 2018 readers

What were readers interested in during 2018? Java developer tutorials topped the list. Learn how to integrate RESTful APIs, GIT, ...

## SearchDataCenter

• ### Top five data center trends to watch in 2019

In 2019, data center admins should research how technologies such as AIOps, chatbots and GPUs can help them with their management...

• ### Top server maintenance tips to extend hardware lifespan

IT administrators can upgrade CPU, RAM and networking hardware to maintain smooth server operations and to maximize resources.

• ### IBM quantum computing system, apps lab seek commercial users

IBM finally introduced its first integrated quantum computer that is designed for commercial accounts. But the emergence of ...

## SearchContentManagement

• ### Open source search engines attract developers

The enterprise search market is evolving and offering new options, such as open source and AI. Discover the benefits of these ...

• ### Successful chatbot content strategies rely on tools, AI

Chatbots aren't a panacea curing customer support issues. Organizations must hone their content quality, organization and ...

• ### Be aware of new OneDrive and SharePoint security features

SharePoint and OneDrive shops can implement recent security features to improve the content sharing process. Discover what's new ...

## SearchHRSoftware

• ### 5 common examples of employee feedback for HRMS projects

Ongoing employee feedback can provide your organization with insight into HRMS features that are not working correctly and new ...

• ### Phishing attacks are top employee data breach threat for HR

HR and finance department employees are targets for more sophisticated phishing attacks. These departments collect and store ...

• ### Government shutdown may sink workplace engagement, hike turnover

The impact of the partial government shutdown on federal employees will unfold long after it ends, creating an HR nightmare. ...

Close