• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 977
  • 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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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