# Scripts for dates, days and holidays

## A compilation of expert responses from Frank Kulash on coding for dates, days and holidays.

 Frank Kulash Frank Kulash is our expert in SQL and PL/SQL. Frank is also an Oracle database administrator for a Massachusetts state agency. If you have a question for Frank, enter it here. Also, if you are looking for more on SQL and PL/SQL, view more of Frank's expert answers.

SearchOracle.com SQL and PL/SQL expert Frank Kulash fields many questions about working with dates, days and holidays in Oracle. Check out this collection of scripts and tips to help solve your coding dilemmas.

Calculating approval date

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

Frank Kulash: So the Approvals Office promises results, sometimes in 0 days, sometimes in 1 day, and so on, but the 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;
```

Click for more on calculating and counting dates.

Calculating AVG date

Do you know an efficient way to calculate AVG date in Oracle9i?

Frank Kulash: The bad news is: AVG only works on numbers. If you need to average anything else, the best you can do is map the values to numbers, average the numbers and, if necessary and possible, map back to the original domain.

The good news is: That's quite easy with dates in any version of Oracle. Oracle's date arithmetic does it all for you. You don't even have to use TO_NUMBER or TO_DATE.

CLICK for more on AVG date.

Dates and DECODE

How do I use the DECODE function with a date field?

Frank Kulash: DECODE only does exact matching. For example, exact values representing "Today" and "Tomorrow" are easy to get, so you might use DECODE like this:

``` SELECT due_dt, DECODE ( TRUNC (due_dt), TRUNC (SYSDATE), 'Today', TRUNC (SYSDATE + 1), 'Tomorrow', 'Neither' ) AS due_text FROM tablex;
```

When you want to compare values (e.g., "Is date a before date b?," or "Is date a between dates b1 and b2?"), then the CASE statement is much more convenient.

``` SELECT due_dt, CASE WHEN TRUNC (due_dt) < TRUNC (SYSDATE) THEN 'Past' WHEN TRUNC (due_dt) BETWEEN TRUNC (SYSDATE) AND TRUNC (SYSDATE) + 6 -- Note: BETWEEN includes both end-points, so -- SYSDATE+0 through SYSDATE+6 is a full week THEN 'This Week' ELSE 'Later' END AS due_text FROM tablex;
```

Click for more on dates and DECODE.

Determining day of the week

I need to determine if a particular day of the week exists between two given dates. Is there an Oracle DATE function that does this?

Frank Kulash: There is an Oracle function, NEXT_DAY, that you can use in a fairly elegant solution.

``` CREATE OR REPLACE FUNCTION dow_between ( in_dow_text VARCHAR2, -- e.g. 'Mon' or 'Monday' in_date_1 DATE, -- starting date in_date_2 DATE -- ending date ) RETURN DATE -- ***************************** -- ** d o w _ b e t w e e n ** -- ***************************** -- dow_between returns the a DATE between in_date_1 and -- in_date_2 whose day-of-the week matches in_dow_text. -- dow_between returns NULL if there is no such date. -- in_date_1 must be earlier than in_date_2. -- Example: dow_between ('SAT', SYSDATE, due_dt) -- checks if there is a Saturday between today and due_dt, -- inclusive. If there is, it returns the DATE of the first -- one. If not, it returns NULL. -- in_dow_text can be any unique abbreviation (case insensitive) -- of the day-of-the-week in NLS_LANGUAGE. IS return_date DATE := NEXT_DAY ( in_date_1 - 1, in_dow_text ); BEGIN IF TRUNC (return_date) > in_date_2 THEN return_date := NULL; END IF; RETURN return_date; END dow_between; /
```

Click for more on checking day of the week.

Testing for holidays

How can I modify your solution/a> for counting the number of work days so it doesn't count dates in my holiday table?

Frank Kulash: If you have a function that takes a date as an argument and returns one value (e.g. a positive number) if that date is a holiday, or another value (e.g. 0) if it's not a holiday, simply add it to the IF statement that tests if you want to skip a date:

``` 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') OR holiday_num (in_event_dt) > 0 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;
```

If you don't have a function like that, click here to get one.

Featured Topic: Top SQL and PL/SQL advice
Best Web Links: Languages and development

## SearchDataManagement

• ### Oracle brings GoldenGate data integration service to cloud

Oracle is making its GoldenGate real time data technology available on its second-generation Oracle Cloud Infrastructure platform...

• ### Era Software raises \$15.25M for enterprise data management

The startup that began as the EraDB time series database is advancing its efforts with new funding and a cloud service for its ...

• ### Dgraph GraphQL database users detail graph use cases

Graph DB vendor Dgraph Labs is expanding its AWS cloud footprint with new regions and adding change data capture capabilities in ...

• ### TigerGraph unveils support for GCP, adds new connectors

Graph database vendor TigerGraph unveiled support for Google Cloud and new connectors to Snowflake and Tableau on April 21 during...

• ### Startup Veezoo emerges from stealth with NLQ-based platform

Aiming to be 'Siri for enterprises,' an analytics startup emerged from stealth with a platform that enables users to interact ...

• ### 15 data science tools to consider using in 2021

Numerous tools are available for data science applications. Read about 15, including their features, capabilities and uses, to ...

## SearchSAP

• ### S/4HANA Cloud SaaS ERP: Buying team overview

SAP's multi-tenant SaaS ERP, S/4HANA Cloud, is a viable choice for companies that need ease in their infrastructure management. ...

• ### SAP forms financial services partnership with Dediq

SAP and financial industry investment firm Dediq are forming a new business unit to develop applications that help banks and ...

• ### Unpatched applications threaten SAP security

Cyberattacks are a significant threat to unpatched, unprotected SAP applications, according to a new threat intelligence report ...

## SearchSQLServer

• ### SQL Server database design best practices and tips for DBAs

Good database design is a must to meet processing needs in SQL Server systems. In a webinar, consultant Koen Verbeeck offered ...

• ### SQL Server in Azure database choices and what they offer users

SQL Server databases can be moved to the Azure cloud in several different ways. Here's what you'll get from each of the options ...

• ### Using a LEFT OUTER JOIN vs. RIGHT OUTER JOIN in SQL

In this book excerpt, you'll learn LEFT OUTER JOIN vs. RIGHT OUTER JOIN techniques and find various examples for creating SQL ...

## TheServerSide.com

• ### Incorporate diversity and inclusion in technology design

DEI in technology is about more than creating a diverse workplace. We talked to a few DEI professionals about how teams build ...

• ### Microsoft previews OpenJDK distro to the delight of devs

In a move meant to attract more Java developers to its Azure cloud and further support the Java community, Microsoft launched a ...

• ### Supreme Court ruling on Java APIs eases developer worries

Now that the Supreme Court has ruled for Google over Oracle in their high-stakes copyright battle over Java APIs, developers can ...

## SearchDataCenter

• ### Nvidia SDK simulates quantum computing circuits on GPU systems

Nvidia edged its way into the quantum computing market with an SDK that simulates quantum circuits by adding horsepower to ...

• ### Programmable processor technology for next-gen data centers

The right processing technology can benefit your data center. Learn about advancements in CPU technologies, recent vendor ...

• ### Data processing units accelerate infrastructure performance

DPUs often run on networking packets to move information in the data center, instead of supporting processing workflows. Get an ...

## SearchContentManagement

• ### Hyland gets digital asset management tech with Nuxeo buy

By acquiring a smaller competitor's digital asset management platform, Hyland looks to build on its 2020 purchase of Alfresco, ...

• ### OpenText releases Cloud Editions content services updates

OpenText CE 21.2 includes federated document compliance that extends to Microsoft Office 365, along with a revamped content ...

As the pandemic disrupts paper workflows, Adobe courts small business users with simple webforms, digital signatures and payments...

## SearchHRSoftware

• ### Shift to HR shared services could save Connecticut millions

By consolidating 17 separate HR operations into one shared service, Connecticut expects to save significantly on costs, most of ...

• ### 10 steps to support your HCM system post go-live

To ensure a new system's success, HR leaders need to develop a plan for how they will support their new HCM system post go-live. ...

• ### Face mask detection a newcomer to employee surveillance

Face mask detection has emerged as another form of employee surveillance technology, and its adoption may be helped indirectly by...

Close