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

• ### Chief data officer skills tested by AI tech blitz

A reporter's notebook from a recent MIT symposium provides insights on chief data officer needs, as the AI wave starts to hit ...

• ### Open source database software finally gets down to business

Once used primarily in web applications, open source databases are finding a place in more mission-critical business systems -- a...

• ### Open source RDBMS uses spurred by lower costs, cloud options

Open source technologies have become more capable alternatives to mainstream relational databases for cost-conscious users -- and...

• ### Reddit and the aspiring data scientist

For amateur data scientists, Reddit provides the opportunity to post about personal projects, chat with like-minded people and ...

• ### Talking Data: AI advances and the MIT Startup Exchange

The Talking Data podcast team discusses some of the MIT startups that are making advances in AI and natural language processing.

• ### Tableau BI gets Extensions API in version 2018.2 update

Tableau's BI software sees general release of a 2018.2 update with an API for dashboard extensions, while a 2018.3 beta adds more...

## SearchSAP

• ### S/4HANA supply chain software integration options

Starting with S/4HANA supply chain software and adding additional capabilities through components such as Ariba and C/4HANA can ...

• ### SAP S/4HANA Cloud update adds functionality for two industries

SAP focuses on manufacturing and professional services with the new features it's adding to S/4HANA Cloud. The enhancements are a...

• ### French transit develops chatbot with SAP Conversational AI

The transit system in the Paris metropolitan area is tackling an AI-driven chatbot project to create an easy way for riders to ...

## SearchSQLServer

• ### 2 ways to attach SQL Server database files to Linux containers

SQL Server files can be stored outside of Docker containers in host directories or volumes. Here's how to set up SQL Server on ...

• ### Get started with SQL Operations Studio in 7 steps

Microsoft's newest tool, SQL Operations Studio, provides DBAs with a free, lightweight option for managing SQL Server and Azure ...

• ### Quick start tips for using the new MSSQL-CLI SQL query tool

The new MSSQL-CLI command-line tool provides many enhancements over SQLCMD CLI that enable you to quickly write and run T-SQL ...

## TheServerSide.com

• ### 10 Git quiz questions to test your distributed version control skills

Test your distributed version control knowledge with this 10 Git questions quiz. It's a quick test that will see how well you ...

• ### Meaning of inversion of control in Spring and Java: IoC explained

What is inversion of control, and why has inversion of control in Spring and Java become such a core development concept?

• ### Ten Jenkins quiz questions to test your install and config IQ

Here are 10 Jenkins questions designed to test your knowledge of the build tool. Think you know continuous integration well? Take...

## SearchDataCenter

• ### Converged data center migration practices to avoid data loss

Refreshing data center hardware is no simple task, especially for organizations with multiple workflows. Before completing an ...

• ### 14 SIEM reports and alerts to boost security

Setting up an SIEM reporting infrastructure requires an overall knowledge of what data capture is available. With standard ...

• ### IBM Power9 servers add reliability, reduce maintenance costs

IBM looks to grab high-end hardware market share from archrival Intel with the introduction of two Power9 servers that offer ...

## SearchContentManagement

• ### Box AI, workflow automation strategies about to unfold

Box is set to reveal new AI and workflow automation tools to add to its cloud content services platform -- technology seen as key...

• ### Office 365 SharePoint Online features raise CIO concerns

Most of Office 365's products can help solve specific challenges facing CIOs, but they also seem to compete directly with some of...

• ### Content services platforms are the new incarnation of ECM

A tribal council is preserving British Columbia's native languages with the help of a newer type of content management platform ...

## SearchHRSoftware

• ### Gartner analyst sees limit to tech's ability to fix hiring bias

The best approach to fixing hiring bias is through an analysis of actual hiring data, a Gartner analyst advises. New technologies...

• ### Employee turnover prediction takes aim at rising quit rate

The economy is encouraging employees to look for new jobs, and the quit rates are rising. HR has a variety of tools and methods ...

• ### 5 keys to ensuring your new SaaS HR system excels

Running HR in the public cloud brings data, integration and change management issues. Here are tips on overcoming the biggest ...

Close