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

# Calculating time differences, excluding weekends

I have a table containing following Columns plus other columns:

``` Start Date 04-01-2002(mm-dd-yyyy)  		varchar(10)
Start Time column 09:30:30(hh:mm:ss)  	 varchar(8)
End Date 04-05-2002(mm-dd-yyyy)        	varchar(10)
End Time column 09:45:30(hh:mm:ss)   	varchar(8)
```
1. We want to create a view for report users, so that they could just select the other columns and difference in start Date/time to End Date/Time.
2. The difference in time should be based on business day. i.e. Saturday & Sunday should be excluded.
3. Each business day is defined as 8:00 AM to 5:00 PM (1 business day = 9 hrs)
4. The time is stored as a 24-hour clock.

For example:

```Start Date   Start Time		End Date       End Time      Desired Output
04/01/2002   10:00:00		04/01/2002     3:30:00	       0 Days  5:30 Hrs
04/01/2002   16:00:00		04/02/2002     10:45:00	       0 Days  3:45 Hrs
04/01/2002   09:00:00		04/03/2002     10:45:00	       2 Days  1:45 Hrs
04/05/2002   16:30:00		04/08/2002     9:45:00	       0 Days  2:15 Hrs*

*In last case as 04/06/2002 & 04/07/2002 were Saturday & Sunday, hence
excluded. So the difference of time is just 2:15 hrs.
```
Can you please give me a suggestion/pseudo code/logic on how I might do this? We cannot use cursors as we want to make a view.

This type of arithmetic isn't going to work very easy since you are not using Oracle's native DATE datatype. If you used one column of the DATE datatype to denote the starting time and date, and another column to denote the ending date and time, then you could just subtract the two values and it would tell you the difference! The DATE datatype makes this really easy.

The next trick is to then figure out how many weekends are in that date range and subtract 2 days for the weekend. The DECODE function can be used in your query to perform if/then analysis and return different results.

#### For More Information

• What do you think about this answer? E-mail the editors at editor@searchDatabase.com with your feedback.
• The Best Oracle Web Links: tips, tutorials, scripts, and more.
• Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
• Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
• Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.

This was last published in April 2002

#### Have a question for an expert?

Please add a title for your question

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

• ### The main picks for Hadoop distributions on the market

Check out the current top Hadoop distribution vendors in the market to help you determine which product is best for your company.

• ### Inside view of Tibco integration architecture planning

Tibco's acquisitions of well-regarded, small software specialists such as SnappyData are part of a drive toward what it calls '...

• ### Lumina launches Radiance, a data risk management platform

In an effort to prevent data loss, Lumina launched Radiance, a SaaS data risk management platform. It collects and analyzes data ...

## SearchBusinessAnalytics

• ### Latest Tableau BI software update upgrades map technology

Tableau Software has released Tableau 2019.2, an update to previous BI software. It includes upgrades to location data, new ...

• ### McDonald's orders up customer service analytics, shakes up fast food

The fast-food giant is acquiring Dynamic Yield, a big data analytics platform, in pursuit of a more personalized customer ...

• ### Sisense acquisition of Periscope yields versatile BI platform

The Sisense merger with Periscope Data is expected to create a new, combined BI product that will cater to casual business users ...

## SearchSAP

• ### SAP public cloud strategy leans heavily on big-name partners

Analyst Holger Mueller says SAP is smart to stop trying to compete as a cloud infrastructure provider so it can leave that job to...

• ### Patch early, patch often to manage SAP exploit

In this Q&A, Onapsis CEO Mariano Nunez and SAP security head Tim McKnight discuss the recent SAP security system threat and what ...

• ### Plattner: Cloud is the way forward for S/4HANA systems

In this Q&A, SAP's Hasso Plattner explains why the cloud is the best option for SAP S/4HANA systems and why companies must know ...

## SearchSQLServer

• ### Check SQL Server Query Store performance impact before using

Many IT teams hesitate to use SQL Server Query Store due to performance concerns. Consultant Andy Warren offers tips on how to ...

• ### SQL Server vs. MySQL: Learn the differences

MySQL and Microsoft SQL Server relational databases have their pros and cons. Weigh the differences between SQL Server and MySQL ...

• ### SQL Server performance tuning best practices for DBAs

Tuning database performance is a complex process, but consultant Joey D'Antoni details a list of SQL Server performance tuning ...

## TheServerSide.com

• ### Master Git basics and branch into DVCS

This Git guide focuses on some of the most basic information and tips. With a solid foundation, developers will be better ...

• ### Don't let plugins open up more Jenkins vulnerabilities

Let's explore the Blue Ocean, Config File Provider and Groovy plugins with security vulnerabilities. Here's how to address them, ...

• ### NetBeans Java IDE becomes top-level Apache project

The NetBeans Java development environment has achieved top-level project status at the Apache Software Foundation, a leading open...

## SearchDataCenter

• ### How do I size a UPS unit?

Your data center UPS sizing needs are dependent on a variety of factors. Develop configurations and determine the estimated UPS ...

• ### How to enhance FTP server security

If you still use FTP servers in your organization, use IP address whitelists, login restrictions and data encryption -- and just ...

• ### 3 ways to approach cloud bursting

With different cloud bursting techniques and tools from Amazon, Zerto, VMware and Oracle, admins can bolster cloud connections ...

## SearchContentManagement

• ### Box workflow automation software, Box Relay, overhauled

Content management software vendor Box makes over Box Relay, integrating the workflow automation system more fully into the Box ...

• ### Fluid Framework opens the door to new Microsoft capabilities

Fluid Framework provides multiuser co-authoring capabilities -- offering never-seen-before flexibility. It also has the potential...

• ### The power of enterprise collaboration software and tools

Research indicates that companies are snapping up enterprise collaboration software. But do they know how to boost worker ...

## SearchHRSoftware

• ### HR data issues caused by lack of customization in HR systems

As more HR systems move to the cloud, organizations may be unable to customize certain features to ensure accurate data capture. ...

• ### Improved HR security may be why W-2 scams are down

HR's focus on better securing employee data may be working. In its annual data breach investigations report, Verizon found a ...

• ### Manage information overload with AI, insight curation tools

With information increasingly spread across several sources, knowledge workers often struggle to find what they need quickly. ...

Close