Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 979
  • Last Modified:

Date Count without Weekend

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
bob318
Asked:
bob318
1 Solution
 
BulZeyECommented:
what do you want to count?? days possibly??  are you passing in the start and end???
0
 
bob318Author Commented:
I want to count the business date between 2 data parameter

0
 
taiskCommented:
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
 
LowfatspreadCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now