Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Date Count without Weekend

Posted on 2004-04-16
4
Medium Priority
?
976 Views
Last Modified: 2008-03-17
Anyone has idea how to do this?
If I create a function to do this, will that affect the performance of the query a lot? Thank you
0
Comment
Question by:bob318
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 4

Expert Comment

by:BulZeyE
ID: 10845803
what do you want to count?? days possibly??  are you passing in the start and end???
0
 

Author Comment

by:bob318
ID: 10845846
I want to count the business date between 2 data parameter

0
 
LVL 3

Accepted Solution

by:
taisk earned 200 total points
ID: 10846600
If the database is Oracle,

CREATE OR REPLACE FUNCTION daysbetween
  (dateFrom in date, dateTo in date)
return number
IS
begin
declare
      days number;
begin
        days := dateTo-dateFrom+1;
        for i in 0 .. days-1 loop
              -- In my locale, the week starts Sun so Sun = 1, Sat = 7
              if to_char(dateFrom+i, 'd') in (1, 7) then
                      days := days - 1;
            end if;                      
        end loop;
        return days;
end;
end;

Obviously the function would not take public holidays into account.  You can easily modify it to look up a table that stores the dates for public holiday.

Similar logic may be used for SQL server and Access.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 10848417
if you want to ount business days then you need a calendar table which tells you which days are valid
and then join that to your queries and count....

thats the only way your going to be able to account for public holidays or even half day openings ...

 
0

Featured Post

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question